4

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

 7 months ago
source link: https://blog.51cto.com/u_13423706/9580049
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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

精选 原创

我是青石路 2024-02-04 11:05:39 博主文章分类：SQL ©著作权

文章标签 SQL MySQL PostgreSQL 文章分类 MySQL 数据库 阅读数191

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

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

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

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

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

重点是标准 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

小计与合计

小计与合计

小计与合计

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

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

　　GROUP BY + 应用程序汇总

GROUP BY

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

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

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

　　是不是有面试內味了？

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

　　GROUP BY + UNION ALL

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

UNION

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

UNION ALL 和 UNION

SQL 实现了 小计与合计

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

SELECT

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

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

　　ROLLUP

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

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

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

Null 当 合计

Null 处理成 合计

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

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

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

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

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

WITH 是 MySQL

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

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

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

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

GROUPING 、 ROLLUP

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

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

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

GROUPING

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

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

　　ROLLUP

ROLLUP

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

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

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

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

registration_date

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

　　就问你们看的懵不懵？

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

　　反正我有 2 点比较懵：

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

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

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

ROLLUP

ROLLUP

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

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

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

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

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

GROUPING

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

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

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

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

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

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

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

ROLLUP 和 CUBE 相比， GROUPING

　　GROUPING

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

　　ROLLUP

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

GROUP BY

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

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

　　同样做个等价替换

GROUP BY

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

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

　　《SQL基础教程》


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK