神奇的 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 TABLEtbl_ware (

  ware_idINT PRIMARY KEY,

  ware_nameVARCHAR(100) NOT NULL,

  ware_categoryVARCHAR(100) NOT NULL,

  sale_unit_priceINT,

  purchase_unit_priceINT,

  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
实现了
小计与合计
？

但是，这可恶的
但是
来了

执行 2 次几乎相同的
SELECT
语句，再将其结果进行连接，你们不觉得繁琐吗？

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

面试官又会接着问了：在只用
SQL
的前提下，有没有更合适的实现方法？

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

ROLLUP

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

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

可能有小伙伴会说：这不能算实现了，没看到那么明显的
Null
吗？

如果非要较真的话，这么说也有道理，但是假若我们在展现层（比如前端）将
Null
当
合计
处理了？

为什么我不说在后端将
Null
处理成
合计
？

如果我们在后端将
Null
处理成
合计
，为什么不直接用方式：
GROUP
BY
+
应用程序汇总
？

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

那就把它干掉，调整下
SQL

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

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

1、
WITH
ROLLUP
是
MySQL
的独有写法

ROLLUP
的标准写法是
GROUP
BY
ROLLUP(列名1,列名2,...)
，例如在
PostgreSQL
实现小计与合计

主流的关系型数据库（
Oracle
、
SQL Server
、
DB2
、
PostgreSQL
）都是按
SQL
标准来实现的

唯独
MySQL
没有按标准来，她发挥了她的小任性，用
WITH
ROLLUP
坚持了自己的个性

2、
GROUPING
、
ROLLUP
，你认识吗

这是本文的
重点
（呼应开头了），请继续往下看

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

GROUPING

考虑到
MySQL
8.0
.
30
不支持
CUBE
和
GROUPING
SETS
，所以后面的
SQL
都基于
PostgreSQL
14.1

GROUPING
不会单独使用，往往会结合
ROLLUP
、
CUBE
和
GROUPING
SETS
其中之一来使用

ROLLUP

关于
ROLLUP
，前面已经演示了一个案例

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

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

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

前面的案例只有一个聚合列，如果再加一列
registration_date
，会是什么结果？

就问你们看的懵不懵？

反正我有 2 点比较懵：

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

2、这么多
Null
，分别表示什么

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

至此，相信大家对
ROLLUP
的作用有一定感觉了

总结下，
ROLLUP
作用就如其名一样，能够得到像从小计到合计，从最小的聚合级开始，聚合单位逐渐扩大的结果

GROUP
BY
ROLLUP(ware_category)
时，那么结果就是以
ware_category
归类的
小计
加上这些
小计
的
合计
，一共 3 + 1 = 4 条记录

GROUP
BY
ROLLUP(ware_category,registration_date)
时，那么结果就是以
ware_category,registration_date
归类的
小计
加上
GROUP
BY
ROLLUP(ware_category)
的结果，一共 9 + 4 = 13 条记录

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

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

所以为了避免混淆，
SQL
标准就规定用
GROUPING
函数来判断超级分组记录的
NULL

如果
GROUPING
函数的值是 1，则表示是超级分组记录，0 则表示其他情况

我们调整下
SQL

SELECT 
    CASE WHEN GROUPING(ware_category) = 1
        THEN '商品类别 合计'
        ELSEware_categoryEND ASware_category,CASE WHEN GROUPING(registration_date) = 1
        THEN '登记日期 合计'
        ELSE TO_CHAR(registration_date, 'YYYY-MM-DD')END    ASregistration_date,SUM(purchase_unit_price) ASpurchase_unit_pricesFROMtbl_wareGROUP BYROLLUP(ware_category,registration_date)ORDER BY ware_category DESC, registration_date;

View Code

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

CUBE

语法和
ROLLUP
一样，我们直接看案例

SELECT 
    CASE WHEN GROUPING(ware_category) = 1
        THEN '商品类别 合计'
        ELSEware_categoryEND ASware_category,CASE WHEN GROUPING(registration_date) = 1
        THEN '登记日期 合计'
        ELSE TO_CHAR(registration_date, 'YYYY-MM-DD')END    ASregistration_date,SUM(purchase_unit_price) ASpurchase_unit_pricesFROMtbl_wareGROUP BYCUBE(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 是聚合键的个数）

本例中，聚合键有 2 个（
ware_category,registration_date
），所以组合个数就是 2 的 2 次方，即 4 个

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

反观
ROLLUP
，组合个数就是 n + 1

提个疑问，
ROLLUP
的结果一定包含在
CUBE
的结果之中吗？

GROUPING SETS

该运算符主要用于从
ROLLUP
或者
CUBE
的结果中取出部分记录

例如，如果希望从
GROUP
BY
CUBE(ware_category,registration_date)
的结果中选出
商品类别
和
登记日期
各自作为聚合键的结果

可以这么实现

SELECT 
    CASE WHEN GROUPING(ware_category) = 1
        THEN '商品类别 合计'
        ELSEware_categoryEND ASware_category,CASE WHEN GROUPING(registration_date) = 1
        THEN '登记日期 合计'
        ELSE TO_CHAR(registration_date, 'YYYY-MM-DD')END    ASregistration_date,SUM(purchase_unit_price) ASpurchase_unit_pricesFROMtbl_wareGROUP BY GROUPING SETS (ware_category,registration_date);

View Code

提个问题，有
Null
的哪一行记录表示什么？

相比
ROLLUP
和
CUBE
相比，
GROUPING
SETS
的使用场景特别少，有所了解即可

总结

GROUPING

作用很明显，就是为了区分
超级分组记录
的
NULL
和原始数据
NULL

说白了，就是为了标识出
合计
记录

ROLLUP

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

GROUP
BY
ROLLUP(ware_category,registration_date)
等价于

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

CUBE

同样做个等价替换

GROUP
BY
CUBE(ware_category,registration_date)
等价于

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

参考

《SQL基础教程》

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

开心一刻

环境准备

小计与合计

GROUP BY + 应用程序汇总

GROUP BY + UNION ALL

ROLLUP

GROUPING

ROLLUP

CUBE

GROUPING SETS

总结

GROUPING

ROLLUP

CUBE

参考

添加新评论

最新文章

最近回复

分类

归档

其它