1.引言
在业务中各种各样的需求都会出现，不少是看起来只是样式的改变却让代码变化巨大。在一些需要层级统计的需求下就涉及到分组小计再合计的需求，比如在各项指标上不仅分公司需要统计，总公司也需要了解，而且最后的报表是将以上数据展现在一起的。
2 效果示例
最一般的统计通常就是二维矩阵，某种程度上就是直接把数据库里的表经简单处理直接展示在交互界面上。但如果分组小计后再总计就比较麻烦，这个需求变化表现在界面上如下图：
3 实验数据
实验中设计了两张表，一是交易信息表，另一是交易单详情表，表结构及数据如下：
-- 交易单详情表：alex_sninfo
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for alex_sninfo
-- ----------------------------
DROP TABLE IF EXISTS `alex_sninfo`;
CREATE TABLE `alex_sninfo` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '交易明细流水号', `sn` bigint(20) unsigned zerofill NOT NULL COMMENT '交易单流水号', `goodsCode` varchar(8) NOT NULL COMMENT '商品编码', `goodsPrice` double(12,2) NOT NULL DEFAULT '0.00' COMMENT '商品单价', `goodsCount` int(11) NOT NULL DEFAULT '0' COMMENT '商品数量', `goodsTotal` double(12,2) NOT NULL DEFAULT '0.00' COMMENT '原价', `discountRate` double(3,2) DEFAULT NULL COMMENT '折扣率', `payTotal` double(12,2) NOT NULL DEFAULT '0.00' COMMENT '实价', `goodsComment` varchar(100) DEFAULT NULL COMMENT '商品备注', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of alex_sninfo
-- ----------------------------
INSERT INTO `alex_sninfo` VALUES ('1', '00000000000000000001', 'SH000001', '20.00', '1', '20.00', '1.00', '20.00', '说明1');
INSERT INTO `alex_sninfo` VALUES ('2', '00000000000000000001', 'SH000002', '15.00', '2', '30.00', '1.00', '30.00', '');
INSERT INTO `alex_sninfo` VALUES ('3', '00000000000000000002', 'SH000003', '13.00', '1', '13.00', '0.90', '11.70', '说明1');
INSERT INTO `alex_sninfo` VALUES ('4', '00000000000000000002', 'SH000001', '20.00', '1', '20.00', '0.90', '18.00', '');
INSERT INTO `alex_sninfo` VALUES ('5', '00000000000000000002', 'SH000004', '7.00', '2', '14.00', '0.90', '12.60', '');
INSERT INTO `alex_sninfo` VALUES ('6', '00000000000000000003', 'SH000002', '15.00', '2', '30.00', '0.85', '25.50', '说明1');
INSERT INTO `alex_sninfo` VALUES ('7', '00000000000000000003', 'SH000005', '8.00', '1', '8.00', '0.85', '6.80', '');
INSERT INTO `alex_sninfo` VALUES ('8', '00000000000000000004', 'SH000005', '8.00', '2', '16.00', '1.00', '16.00', '说明2');
INSERT INTO `alex_sninfo` VALUES ('9', '00000000000000000005', 'SH000006', '11.50', '1', '11.50', '0.95', '10.93', '');
INSERT INTO `alex_sninfo` VALUES ('10', '00000000000000000005', 'SH000003', '13.00', '1', '13.00', '0.95', '12.35', '');
INSERT INTO `alex_sninfo` VALUES ('11', '00000000000000000006', 'SH000002', '15.00', '1', '15.00', '0.90', '13.50', '说明1');
INSERT INTO `alex_sninfo` VALUES ('12', '00000000000000000007', 'SH000001', '20.00', '1', '20.00', '1.00', '20.00', '');
INSERT INTO `alex_sninfo` VALUES ('13', '00000000000000000008', 'SH000005', '8.00', '2', '16.00', '0.85', '13.60', '');
INSERT INTO `alex_sninfo` VALUES ('14', '00000000000000000008', 'SH000006', '11.50', '3', '34.50', '0.85', '29.33', '');
INSERT INTO `alex_sninfo` VALUES ('15', '00000000000000000009', 'SH000002', '15.00', '2', '30.00', '0.70', '21.00', '');
INSERT INTO `alex_sninfo` VALUES ('16', '00000000000000000009', 'SH000008', '25.00', '1', '25.00', '0.70', '17.50', '');
INSERT INTO `alex_sninfo` VALUES ('17', '00000000000000000009', 'SH000003', '13.00', '1', '13.00', '0.70', '9.10', '说明1');
INSERT INTO `alex_sninfo` VALUES ('18', '00000000000000000009', 'SH000001', '20.00', '2', '40.00', '0.70', '28.00', '');
INSERT INTO `alex_sninfo` VALUES ('19', '00000000000000000010', 'SH000006', '11.50', '1', '11.50', '1.00', '11.50', '');
INSERT INTO `alex_sninfo` VALUES ('20', '00000000000000000010', 'SH000002', '15.00', '1', '15.00', '1.00', '15.00', '说明2');
-- 交易信息表：alex_consum
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for alex_consum
-- ----------------------------
DROP TABLE IF EXISTS `alex_consum`;
CREATE TABLE `alex_consum` ( `sn` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '交易流水号', `consumerCode` varchar(8) NOT NULL COMMENT '消费者编码', `consumDate` varchar(10) NOT NULL COMMENT '交易日期', `consumTime` varchar(8) NOT NULL COMMENT '交易时间', `consumTotal` double(10,2) NOT NULL DEFAULT '0.00' COMMENT '交易额度', `payType` tinyint(2) NOT NULL COMMENT '支付类型:0-支付宝，1-微信支付，2-现金交易', `merchantCode` varchar(8) NOT NULL COMMENT '商家编码', `consumComment` varchar(100) DEFAULT NULL COMMENT '交易备注', PRIMARY KEY (`sn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of alex_consum
-- ----------------------------
INSERT INTO `alex_consum` VALUES ('00000000000000000001', '11260801', '2017-06-12', '09:13:23', '50.00', '0', '11002038', '一起酷夏活动');
INSERT INTO `alex_consum` VALUES ('00000000000000000002', '11260800', '2017-06-12', '09:23:45', '42.30', '0', '11002038', '');
INSERT INTO `alex_consum` VALUES ('00000000000000000003', '11260805', '2017-06-13', '10:17:24', '32.30', '1', '11002038', '');
INSERT INTO `alex_consum` VALUES ('00000000000000000004', '11260803', '2017-06-15', '09:33:26', '16.00', '0', '11002038', '端午促销');
INSERT INTO `alex_consum` VALUES ('00000000000000000005', '11260802', '2017-06-15', '09:34:25', '23.28', '1', '11002038', '端午促销');
INSERT INTO `alex_consum` VALUES ('00000000000000000006', '11260800', '2017-06-15', '09:40:47', '13.50', '0', '11002038', '端午促销');
INSERT INTO `alex_consum` VALUES ('00000000000000000007', '11260805', '2017-07-17', '09:11:23', '20.00', '2', '11002038', '');
INSERT INTO `alex_consum` VALUES ('00000000000000000008', '11260873', '2017-07-18', '09:23:22', '33.60', '0', '11002038', '');
INSERT INTO `alex_consum` VALUES ('00000000000000000009', '11260807', '2017-08-01', '09:10:17', '75.60', '0', '11002038', '');
INSERT INTO `alex_consum` VALUES ('00000000000000000010', '11260812', '2017-08-03', '09:13:55', '26.50', '1', '11002038', '');4 实现方案
-- MySQL分组小计再总计
-- 创建基础数据临时表，它存储了所有后面要用到的基础数据
CREATE TEMPORARY TABLE tb1 (SELECT goodsCode,consumerCode,alex_sninfo.sn,goodsTotal,payTotal FROM alex_sninfo LEFT JOIN alex_consum ON alex_sninfo.sn = alex_consum.sn);
-- 创建基础数据临时表的镜像临时表（镜像表是我自己的称呼，其实就是一份结构和数据都一样的表），这是为了后面在同一query语句中多次使用这些基础数据
CREATE TEMPORARY TABLE mirror_tb1 SELECT * FROM tb1;
-- 创建将基础数据和小计结果联合后的临时表
CREATE TEMPORARY TABLE tb2 SELECT * FROM (SELECT * FROM mirror_tb1 UNION ALL (SELECT goodsCode,'小计' consumerCode,sn,sum(goodsTotal) AS goodsTotal,sum(payTotal) AS payTotal FROM tb1 GROUP BY goodsCode) ORDER BY goodsCode,consumerCode ) A;
-- 创建将基础数据和小计结果联合后的临时表的镜像临时表
CREATE TEMPORARY TABLE mirror_tb2 SELECT * FROM tb2;
-- 创建最终结果的临时表
CREATE TEMPORARY TABLE tb3 SELECT * FROM (SELECT * FROM mirror_tb2 UNION ALL (SELECT '','合计' consumerCode,sn,sum(goodsTotal) AS goodsTotal,sum(payTotal) AS payTotal FROM tb2 WHERE consumerCode = '小计')) B;
-- 查询最终结果临时表，得到需要展示的列的数据
SELECT goodsCode 商品编码,consumerCode 统计,round(goodsTotal,2) 原价,round(payTotal,2) 实价 FROM tb3;
-- 撤销相关临时表
DROP TABLE tb3;
DROP TABLE tb2;
DROP TABLE tb1;
DROP TABLE mirror_tb1;
DROP TABLE mirror_tb2;5 过程分析
对于这样的需求，很显然难以用一条MySQL语句就轻松搞定，只能通过详细考虑每一步过程，然后将各步（小计、总计）计算结果拼接在一起形成最后的结果集返回。具体的解决方案有很多，但理解起来相对不够直观明了，上面是我在参照了不少资料后写的，主要用到临时表来存储中间计算结果，需要注意的是在使用临时表时容易发生“can’t reopen”的错误，这是因为临时表使用注意事项中就包含其不能在同一query语句中重复使用。在这里，我是通过将临时表复制多份来操作实现的。关于这个先分组小计再总计的需求应该是可以写成一个通用的存储过程留在以后工作中使用的。
附件：相关的sql脚本