一道关于年度、季度SQL统计查询的面试题

场景描述:

大牛科技公司A表是2006年至2013年的销售数据表,其中adddate是销售时间,company是客户公司,money是销售金额,sales是销售员

A表 - 销售数据表

id adddate company money sales
1 2011-11-11 11:11:11 A 10000 张三
2 2012-11-11 11:11:11 B 5000 王五
3 2013-11-11 11:11:11 A 20000 赵六

大牛公司数据库B表中有2006至2013的销售产品表,aid对应A表中的id,pid对应是C表中产品表

B表 - 产品销售表

id aid product pid price number
1 2 AC1100 1 10000 2
2 2 AC1200 1 20000 3
3 1 SSL1100 2 10000 1
4 3 WOC1100 3 10000 1

C表 - 产品类型表

id type
1 AC
2 SSL
3 WOC

问题1:

请使用SQL语句统计2011年至2013年三年中每个季度的销售金额

解决

首页我们得整清楚什么是季度!不清楚找度年扫一下盲(哈哈)。个人认为第一题主要考察SQL中的日期函数以及group by分组查询。

SELECT YEAR('2019-08-17')   //2019
SELECT MONTH('2019-08-17')  //8
SELECT DAY('2019-08-17')  //17
SELECT QUARTER('2019-08-17')  //3  季度
SELECT DAYOFWEEK('2019-08-17') //7  星期

另外在MySQL中有个特殊的规定,即不允许使用列别名作为查询条件,实在要执行,只好把新字段的组成在条件里再实现一遍

据说是因为MySql中列的别名本来是返回结果的时候才显示的.这个还没有得到证实,纯属猜测!

SELECT  
YEAR(`adddate`) AS yer, 
QUARTER(`adddate`) AS jd, 
SUM(money) 
FROM a  
WHERE YEAR(`adddate`) IN ('2011','2012','2013')  
GROUP BY QUARTER(`adddate`),YEAR(`adddate`)

问题2:

请使用SQL语句按如下格式统计2013年销售金额前5位的销售员

no sales money
1 张三 100000
2 王五 90000
3 赵六 80000

解决

不知道是不是考察:order by和group by共用的问题。order by 必须放在group by 后面

SELECT id AS `no`,sales,SUM(`money`) AS money FROM a WHERE YEAR(`adddate`) = '2013' GROUP BY sales ORDER BY money DESC LIMIT 5

问题3:

请用SQL语句统计出哪个产品线的销售金额最多

SELECT c.`type`,MAX(tab.tt) FROM (SELECT pid,SUM(price*number) AS tt FROM b GROUP BY pid) AS tab LEFT JOIN c ON tab.pid = c.`id` 

测试所用SQL,仅供参考

USE `test`;

/*Table structure for table `a` */

DROP TABLE IF EXISTS `a`;

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `adddate` datetime NOT NULL,
  `company` varchar(10) DEFAULT NULL,
  `money` decimal(7,2) DEFAULT NULL,
  `sales` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into `a`(`id`,`adddate`,`company`,`money`,`sales`) values (1,'2011-01-02 00:00:00','A','10000.00','张三'),(2,'2011-07-03 00:00:00','B','50000.00','王五'),(3,'2011-09-03 00:00:00','A','20000.00','赵六'),(4,'2012-07-03 00:00:00','A','10000.00','李四'),(5,'2013-10-03 00:00:00','B','5000.00','麻子'),(6,'2013-07-04 00:00:00','B','6000.00','小柒');

/*Table structure for table `b` */

DROP TABLE IF EXISTS `b`;

CREATE TABLE `b` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product` varchar(20) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL,
  `price` decimal(7,2) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `b` */

insert  into `b`(`id`,`product`,`pid`,`price`,`number`) values (1,'AC1100',1,'10000.00',2),(2,'AC1200',1,'20000.00',3),(3,'SSL1100',2,'10000.00',1),(4,'WOC1100',3,'10000.00',1);

/*Table structure for table `c` */

DROP TABLE IF EXISTS `c`;

CREATE TABLE `c` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `c` */

insert  into `c`(`id`,`type`) values (1,'AC'),(2,'SSL'),(3,'WOC');