一个sql统计需求

有个朋友问我实现一个需求，一张订单表，按照商品类别来统计每个时间段的数据，用的是mysql数据库。

 

表结构

CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comFullName` varchar(255) DEFAULT NULL COMMENT '店名',
  `lb` varchar(50) DEFAULT NULL COMMENT '类别',
  `salesType` varchar(50) DEFAULT NULL COMMENT '销售类型 0-按份  1-按重',
  `salesTime` varchar(50) DEFAULT NULL COMMENT '时间段',
  `salesQty` int(11) DEFAULT NULL COMMENT '购买人数',
  `comPersons` int(11) DEFAULT NULL COMMENT '来客数',
  `reportDate` int(11) DEFAULT NULL COMMENT '时间',
  `account` varchar(100) DEFAULT NULL COMMENT '店类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

 

表数据

INSERT INTO `t_order` (`id`, `comFullName`, `lb`, `salesType`, `salesTime`, `salesQty`, `comPersons`, `reportDate`, `account`) VALUES ('1', '广州圣地直营店', '包类', '1', '11:00前', '1', '1', '20200701', '直营店');
INSERT INTO `t_order` (`id`, `comFullName`, `lb`, `salesType`, `salesTime`, `salesQty`, `comPersons`, `reportDate`, `account`) VALUES ('2', '广州圣地直营店', '饺子类', '1', '11-16点', '1', '1', '20200702', '直营店');
INSERT INTO `t_order` (`id`, `comFullName`, `lb`, `salesType`, `salesTime`, `salesQty`, `comPersons`, `reportDate`, `account`) VALUES ('3', '代理店', '饺子类', '1', '11-16点', '1', '1', '20200703', '代理店');
INSERT INTO `t_order` (`id`, `comFullName`, `lb`, `salesType`, `salesTime`, `salesQty`, `comPersons`, `reportDate`, `account`) VALUES ('4', '广州圣地直营店', '饺子类', '2', '16:00后', '1', '1', '20200704', '直营店');
INSERT INTO `t_order` (`id`, `comFullName`, `lb`, `salesType`, `salesTime`, `salesQty`, `comPersons`, `reportDate`, `account`) VALUES ('5', '中山益华店', '包类', '1', '11:00前', '1', '1', '20200705', '直营店');
INSERT INTO `t_order` (`id`, `comFullName`, `lb`, `salesType`, `salesTime`, `salesQty`, `comPersons`, `reportDate`, `account`) VALUES ('6', '中山益华店', '包类', '1', '11-16点', '1', '1', '20200706', '直营店');
INSERT INTO `t_order` (`id`, `comFullName`, `lb`, `salesType`, `salesTime`, `salesQty`, `comPersons`, `reportDate`, `account`) VALUES ('7', '中山益华店', '包类', '1', '16:00后', '1', '1', '20200707', '直营店');
INSERT INTO `t_order` (`id`, `comFullName`, `lb`, `salesType`, `salesTime`, `salesQty`, `comPersons`, `reportDate`, `account`) VALUES ('8', '佛山万达广场店', '饮品', '1', '16:00后', '0', '0', '20200707', '直营店');

 

 

 

结果集

 

 

需求

个人理解就是根据reportDate(时间)与account(店类型)条件过滤出来数据，然后在这些数据中根据lb(类别)分组，以salesTime(时间段)来统计salesQty(购买人数)得到结果集1，然后还是相同的条件，以salesTime(时间段)来统计comPersons(来客数)得到结果集2，最后将两个结果集拼成一个展示，因为结果集2得到的只有一条数据，所以与结果集1拼接的时候要出现同结果集1同记录数的冗余数据。

 

 

 

实现

select 
    @rownum:=@rownum+1 AS rownum,
    t.*,
    (t.salesQty_11 + t.salesQty_1116 + t.salesQty_16) AS allSalesQty,
    t1.*,
    (t1.comPersons_11 + t1.comPersons_1116 + t1.comPersons_16) AS allComPersons
from
    (SELECT @rownum:=0) r,
    (
        SELECT
            lb,
            GROUP_CONCAT(DISTINCT case when salesType = '1' then '按份' when salesType = '2' then '按重' end order by salesType SEPARATOR '&') AS salesType,
            SUM(IFNULL(case when salesTime = '11:00前' then salesQty end , 0)) AS salesQty_11,
            SUM(IFNULL(case when salesTime = '11-16点' then salesQty end , 0)) AS salesQty_1116,
            SUM(IFNULL(case when salesTime = '16:00后' then salesQty end , 0)) AS salesQty_16
        FROM
            t_order
        WHERE
            reportDate >= 20200701
        AND reportDate <= 20200731
        AND account = '直营店'
        group by lb
    ) t,
    (
        SELECT
            SUM(IFNULL(case when salesTime = '11:00前' then comPersons end , 0)) AS comPersons_11,
            SUM(IFNULL(case when salesTime = '11-16点' then comPersons end , 0)) AS comPersons_1116,
            SUM(IFNULL(case when salesTime = '16:00后' then comPersons end , 0)) AS comPersons_16
        FROM
            t_order
        WHERE
            reportDate >= 20200701
        AND reportDate <= 20200731
        AND account = '直营店'
    ) t1
order by allSalesQty;