神奇的 SQL ，同时实现小计与合计，阁下该如何应对

开心一刻

　　今天，小区有个很漂亮的姑娘出嫁

　　我对儿子说：你要好好学习，认真写作业，以后才能娶到这么漂亮的老婆

　　儿子好像听明白了，思考了一会，默默的收起了作业本

　　然后如释重负的跟我说到：爸，我以后还是不娶老婆了

 环境准备

重点是标准 SQL

　　但理论是理论，事实是事实，大家需要结合当下的实际情况来看问题

MySQL 8.0.30 来讲解，偶尔会插入 PostgreSQL 14.1 ，没有特殊说明的情况下，都是基于 MySQL 8.0.30

MySQL 建表 tbl_ware

CREATE TABLE `tbl_ware` (
  `ware_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `ware_name` VARCHAR(100) NOT NULL COMMENT '商品名称',
  `ware_category` VARCHAR(100) NOT NULL COMMENT '商品类别',
  `sale_unit_price` INT COMMENT '销售单价',
  `purchase_unit_price` INT COMMENT '进货单价',
  `registration_date` DATE COMMENT '登记日期',
  PRIMARY KEY (`ware_id`) USING BTREE
) ENGINE=InnoDB COMMENT='产品';

View Code

PostgreSQL 建表 tbl_ware

CREATE TABLE tbl_ware (
  ware_id INT PRIMARY KEY,
  ware_name VARCHAR(100) NOT NULL,
  ware_category VARCHAR(100) NOT NULL,
  sale_unit_price INT,
  purchase_unit_price INT,
  registration_date DATE
);

INSERT INTO tbl_ware VALUES 
(1, 'T恤衫', '衣服', 100, 50, '2023-12-11'),
(2, '打孔器', '办公用品', 25, 10, '2023-12-13'),
(3, '运动T恤', '衣服', 150, 50, '2023-12-10'),
(4, '菜刀', '厨房用具', 75, 30, '2023-12-15'),
(5, '高压锅', '厨房用具', 600, 200, '2023-12-15'),
(6, '叉子', '厨房用具', 7, 3, NULL),
(7, '菜板', '厨房用具', 98, 30, '2023-12-12'),
(8, '圆珠笔', '办公用品', 5, 2, '2023-12-15'),
(9, '带帽卫衣', '衣服', 150, 90, NULL),
(10, '砍骨刀', '厨房用具', 150, 69, '2023-12-13'),
(11, '羽绒服', '衣服', 800, 200, NULL);

View Code

小计与合计

小计与合计

小计与合计

　　那有哪些实现方式了，我们今天就来盘一盘

　　GROUP BY + 应用程序汇总

GROUP BY

商品类别

　　我敢断定，这种方式肯定是大家用的最多的方式，因为我就是这么用的！

SQL

　　此时如何实现小计和合计，各位该如何应对？

　　是不是有面试內味了？

　　GROUP BY + UNION ALL

SQL

SQL

UNION

SELECT 语句的聚合键不同，一定不会出现重复行，可以使用 UNION ALL

UNION ALL 和 UNION

SQL 实现了 小计与合计

但是

SELECT

　　在我看来不仅繁琐，效率也会因为繁琐而低下

SQL

　　此时，各位又该如何应对？

　　ROLLUP

　　我就不卖关子了，直接上绝招

　　斗胆问一句，这算实现了吗？

Null

Null 当 合计

Null 处理成 合计

Null 处理成 合计 ，为什么不直接用方式： GROUP BY +

Null 看着着实不爽，关键是坑还多：神奇的 SQL 之温柔的陷阱 → 三值逻辑 与 NULL ！

SQL

　　这下完美了吧，从结果上来看是完美了

　　但从整体上来看，我觉得还不够完美，主要有 2 点

WITH 是 MySQL

ROLLUP 的标准写法是 GROUP BY ，例如在 PostgreSQL

Oracle 、 SQL Server 、 DB2 、 PostgreSQL ）都是按 SQL

MySQL 没有按标准来，她发挥了她的小任性，用 WITH

GROUPING 、 ROLLUP

重点（呼应开头了），请继续往下看

　　　　你们不要怀疑我是不是在套娃，请把怀疑去掉，我就是在套娃！

GROUPING

MySQL 8.0.30 不支持 CUBE 和 GROUPING ，所以后面的 SQL 都基于 PostgreSQL 14.1

GROUPING 不会单独使用，往往会结合 ROLLUP 、 CUBE 和 GROUPING

　　ROLLUP

ROLLUP

　　

商品类别 值 NULL 的那一行，没有聚合键，也就相当于没有 GROUP BY 子句，这时会得到全部数据的 合计行

超级分组记录（super group ，虽然听上去很屌，但还是希望大家把它当做未使用 GROUP BY 的 合计行

合计行 的 ware_category 列的键值不明确，所以会默认使用 NULL

registration_date

 

　　就问你们看的懵不懵？

　　反正我有 2 点比较懵：

　　　　1、每一行记录的含义是什么？

Null

　　关于懵点 1，如果大家细看的话，还是能看明白每一行记录的含义的

ROLLUP

ROLLUP

GROUP BY 时，那么结果就是以 ware_category 归类的 小计 加上这些 小计 的 合计

GROUP BY 时，那么结果就是以 ware_category,registration_date 归类的 小计 加上 GROUP BY

　　如果聚合列有 3 列，大家还能明白每一行记录的含义吗

Null 看着确实难受，关键是难以区分：到底是值是 Null ，还是超级分组记录的 Null

SQL 标准就规定用 GROUPING 函数来判断超级分组记录的 NULL

GROUPING

SQL

SELECT 
    CASE WHEN GROUPING(ware_category) = 1
        THEN '商品类别 合计'
        ELSE ware_category
    END AS ware_category, 
    CASE WHEN GROUPING(registration_date) = 1
        THEN '登记日期 合计'
        ELSE TO_CHAR(registration_date, 'YYYY-MM-DD')
    END    AS registration_date,
    SUM(purchase_unit_price) AS purchase_unit_prices
FROM tbl_ware
GROUP BY ROLLUP(ware_category,registration_date)
ORDER BY ware_category DESC, registration_date;

View Code

　　这样看着是不是清晰很多？

　　CUBE

ROLLUP

SELECT 
    CASE WHEN GROUPING(ware_category) = 1
        THEN '商品类别 合计'
        ELSE ware_category
    END AS ware_category, 
    CASE WHEN GROUPING(registration_date) = 1
        THEN '登记日期 合计'
        ELSE TO_CHAR(registration_date, 'YYYY-MM-DD')
    END    AS registration_date,
    SUM(purchase_unit_price) AS purchase_unit_prices
FROM tbl_ware
GROUP BY CUBE(ware_category,registration_date)
ORDER BY ware_category DESC, registration_date;

View Code

ROLLUP 的结果相比， CUBE 结果多了几行记录，而这几行记录就是 GROUP BY(registration_date)

CUBE ，就是将 GROUP BY 子句中的聚合键的 所有可能组合

　　因此，组合的个数就 2 的 n 次方（n 是聚合键的个数）

ware_category,registration_date

　　如果再添加 1 个变为 3 个聚合键的话，那么组合的个数就是 2 的 3 次方，即 8 个

ROLLUP

ROLLUP 的结果一定包含在 CUBE

　　GROUPING SETS

ROLLUP 或者 CUBE

GROUP BY 的结果中选出 商品类别 和 登记日期

　　可以这么实现

SELECT 
    CASE WHEN GROUPING(ware_category) = 1
        THEN '商品类别 合计'
        ELSE ware_category
    END AS ware_category, 
    CASE WHEN GROUPING(registration_date) = 1
        THEN '登记日期 合计'
        ELSE TO_CHAR(registration_date, 'YYYY-MM-DD')
    END    AS registration_date,
    SUM(purchase_unit_price) AS purchase_unit_prices
FROM tbl_ware
GROUP BY GROUPING SETS (ware_category,registration_date);

View Code

Null

ROLLUP 和 CUBE 相比， GROUPING

总结

　　GROUPING

超级分组记录 的 NULL 和原始数据 NULL

合计

　　ROLLUP

　　做个等价替换，方便大家理解

GROUP BY

　　如果是 3 个聚合键了，等价情况是怎么样的？

　　CUBE

　　同样做个等价替换

GROUP BY

　　如果是 3 个聚合键了，等价情况又是怎么样的？

参考

　　《SQL基础教程》