Oracle关于分组小计再合计

其实在之前就写过mysql关于分组小计再合计，只是当时限于知识和眼界所限，所以给了一个不能算是通用的方案的方案，仅仅只是从效果上完成了任务。随着时间的发展，见识到的东西也多了些，近期在使用Oracle，所以下面就以一个Oracle例子来展示下分组小计再合计的实现。

案例说明

【需求案例】：今有两张表存储商家交易情况详细，现欲了解各商家客户所购买商品情况。案例脚本及数据在文末。
表1描述的是交易单详情，结构如下

表2描述的是交易详情，结构如下
[外链图片转存中…(img-DcXJBgLY-1648357069738)]
废话不多说，直接上效果图
[外链图片转存中…(img-5ObUJp5n-1648357069738)]

代码分析

SELECT c.merchantcode 商家编号,
       c.CONSUMERCODE 消费者编号,
       c.GOODSCODE 商品编码,
       sum(c.goodstotal) 商品原价,
       sum(c.paytotal) 商品实价,
       (grouping(c.merchantcode) + grouping(c.CONSUMERCODE) + grouping(c.GOODSCODE)) FLAG,
       decode((grouping(c.merchantcode) + grouping(c.CONSUMERCODE) + grouping(c.GOODSCODE)),
              0,
              '明细',
              1,
              '小计',
              2,
              '合计',
              '总计') 汇总标记
  FROM (SELECT * FROM alex_sn_info b right join alex_consum a on a.sn = b.sn) c
 group by rollup(c.merchantcode, c.CONSUMERCODE, c.GOODSCODE);

这里主要涉及**grouping()和rollup()**两个函数。GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。ROLLUP分组函数可以理解为Group By分组函数封装后的精简用法，它的本质是在分组计算后再union all一个统计所有数据的行。例如：
select group_id,sum(salary) from group_test group by rollup(group_id);
它就等价于：
select group_id,sum(salary) from group_test group by group_id union all select null, sum(salary) from group_test order by 1;

[参考资料] http://blog.itpub.net/519536/viewspace-610995/
案例脚本：

drop table alex_sn_info;
-- 交易单详情表：alex_sn_info
CREATE TABLE alex_sn_info
(
  id varchar2(20) NOT NULL,
  sn varchar2(20) NOT NULL,
  goodsCode varchar2(8) NOT NULL,
  goodsPrice number(12,2) DEFAULT 0.00 NOT NULL ,
  goodsCount number(11) DEFAULT 0 NOT NULL ,
  goodsTotal number(12,2) DEFAULT 0.00 NOT NULL ,
  discountRate number(3,2) DEFAULT 1 NULL ,
  payTotal number(12,2) DEFAULT 0.00 NOT NULL,
  goodsComment varchar2(100) DEFAULT NULL
);

comment on column alex_sn_info.id
  is '交易明细流水号 ';
comment on column alex_sn_info.sn
  is '交易单流水号';
comment on column alex_sn_info.goodsCode
  is '商品编码 ';
comment on column alex_sn_info.goodsPrice
  is '商品单价';
comment on column alex_sn_info.goodsCount
  is '商品数量 ';
comment on column alex_sn_info.goodsTotal
  is '原价';
comment on column alex_sn_info.discountRate
  is '折扣率 ';
comment on column alex_sn_info.payTotal
  is '实价';      
comment on column alex_sn_info.goodsComment
  is '商品备注';     

INSERT INTO alex_sn_info VALUES ('1', '00000000000000000001', 'SH000001', '20.00', '1', '20.00', '1.00', '20.00', '说明1');
INSERT INTO alex_sn_info VALUES ('2', '00000000000000000001', 'SH000002', '15.00', '2', '30.00', '1.00', '30.00', '');
INSERT INTO alex_sn_info VALUES ('3', '00000000000000000002', 'SH000003', '13.00', '1', '13.00', '0.90', '11.70', '说明1');
INSERT INTO alex_sn_info VALUES ('4', '00000000000000000002', 'SH000001', '20.00', '1', '20.00', '0.90', '18.00', '');
INSERT INTO alex_sn_info VALUES ('5', '00000000000000000002', 'SH000004', '7.00', '2', '14.00', '0.90', '12.60', '');
INSERT INTO alex_sn_info VALUES ('6', '00000000000000000003', 'SH000002', '15.00', '2', '30.00', '0.85', '25.50', '说明1');
INSERT INTO alex_sn_info VALUES ('7', '00000000000000000003', 'SH000005', '8.00', '1', '8.00', '0.85', '6.80', '');
INSERT INTO alex_sn_info VALUES ('8', '00000000000000000004', 'SH000005', '8.00', '2', '16.00', '1.00', '16.00', '说明2');
INSERT INTO alex_sn_info VALUES ('9', '00000000000000000005', 'SH000006', '11.50', '1', '11.50', '0.95', '10.93', '');
INSERT INTO alex_sn_info VALUES ('10', '00000000000000000005', 'SH000003', '13.00', '1', '13.00', '0.95', '12.35', '');
INSERT INTO alex_sn_info VALUES ('11', '00000000000000000006', 'SH000002', '15.00', '1', '15.00', '0.90', '13.50', '说明1');
INSERT INTO alex_sn_info VALUES ('12', '00000000000000000007', 'SH000001', '20.00', '1', '20.00', '1.00', '20.00', '');
INSERT INTO alex_sn_info VALUES ('13', '00000000000000000008', 'SH000005', '8.00', '2', '16.00', '0.85', '13.60', '');
INSERT INTO alex_sn_info VALUES ('14', '00000000000000000008', 'SH000006', '11.50', '3', '34.50', '0.85', '29.33', '');
INSERT INTO alex_sn_info VALUES ('15', '00000000000000000009', 'SH000002', '15.00', '2', '30.00', '0.70', '21.00', '');
INSERT INTO alex_sn_info VALUES ('16', '00000000000000000009', 'SH000008', '25.00', '1', '25.00', '0.70', '17.50', '');
INSERT INTO alex_sn_info VALUES ('17', '00000000000000000009', 'SH000003', '13.00', '1', '13.00', '0.70', '9.10', '说明1');
INSERT INTO alex_sn_info VALUES ('18', '00000000000000000009', 'SH000001', '20.00', '2', '40.00', '0.70', '28.00', '');
INSERT INTO alex_sn_info VALUES ('19', '00000000000000000010', 'SH000006', '11.50', '1', '11.50', '1.00', '11.50', '');
INSERT INTO alex_sn_info VALUES ('20', '00000000000000000010', 'SH000002', '15.00', '1', '15.00', '1.00', '15.00', '说明2');
INSERT INTO alex_sn_info VALUES ('21', '00000000000000000011', 'SH000002', '11.50', '3', '34.50', '0.85', '29.33', '说明2');
INSERT INTO alex_sn_info VALUES ('22', '00000000000000000012', 'SH000001', '15.00', '1', '15.00', '1.00', '15.00', '说明2');
INSERT INTO alex_sn_info VALUES ('23', '00000000000000000013', 'SH000006', '13.00', '1', '13.00', '0.95', '12.35', '说明2');


drop table alex_consum;
-- 交易信息表：alex_consum
CREATE TABLE alex_consum (
  sn varchar2(20) NOT NULL, 
  consumerCode varchar2(8) NOT NULL,
  consumDate varchar2(10) NOT NULL,
  consumTime varchar2(8) NOT NULL,
  consumTotal number(10,2) DEFAULT 0.00 NOT NULL,
  payType varchar2(2) NOT NULL,
  merchantCode varchar2(8) NOT NULL,
  consumComment varchar2(100)
);
comment on column alex_consum.sn is '交易单流水号';
COMMENT ON column alex_consum.consumerCode is '消费者编码';
COMMENT ON column alex_consum.consumDate is '交易日期';
COMMENT ON column alex_consum.consumTime is '交易时间';
COMMENT ON column alex_consum.consumTotal is '交易额度';
COMMENT ON column alex_consum.payType is '支付类型:0-支付宝，1-微信支付，2-现金交易';
COMMENT ON column alex_consum.merchantCode is '商家编码';
COMMENT ON column alex_consum.consumComment is '交易备注';

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', '11002012', '端午促销');
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', '11002023', '');
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-09-03', '09:13:55', '26.50', '1', '11002023', '');
INSERT INTO alex_consum VALUES ('00000000000000000011', '11260800', '2017-09-15', '09:40:47', '13.50', '0', '11002012', '端午促销');
INSERT INTO alex_consum VALUES ('00000000000000000012', '11260805', '2017-10-17', '09:11:23', '20.00', '2', '11002023', '');
INSERT INTO alex_consum VALUES ('00000000000000000013', '11260873', '2017-11-18', '09:23:22', '33.60', '0', '11002012', '');

commit;

-- 统计商家各消费者购买商品的具体情况
-- <商家编号，消费者会员编号，商品编号>
SELECT c.merchantcode 商家编号,
       c.CONSUMERCODE 消费者编号,
       c.GOODSCODE 商品编码,
       sum(c.goodstotal) 商品原价,
       sum(c.paytotal) 商品实价,
       decode((grouping(c.merchantcode) + grouping(c.CONSUMERCODE) + grouping(c.GOODSCODE)),
              0,
              '明细',
              1,
              '小计',
              2,
              '合计',
              '总计') 汇总标记
  FROM (SELECT * FROM alex_sn_info b right join alex_consum a on a.sn = b.sn) c
 group by rollup(c.merchantcode, c.CONSUMERCODE, c.GOODSCODE);