MySQL分组语句

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊Mysql的分组语句

测试数据:

CREATE  TABLE testa (area   VARCHAR(20), month VARCHAR(20),
amount int);

insert into testa values ('上海', '1月', 199); 
insert into testa values ('上海', '2月', 199); 
insert into testa values ('上海', '3月', 155); 
insert into testa values ('上海', '3月', 155); 
insert into testa values ('上海', '4月', 125); 
insert into testa values ('广州', '1月', 75); 
insert into testa values ('广州', '2月', 67); 
insert into testa values ('北京', '1月', 235);
insert into testa values ('北京', '2月', 330); 
Commit;

一.group by 语句

-- 按地区和月份进行汇总，求总的销售额
SELECT  area, month, SUM(amount) FROM testa
GROUP BY   area, month ORDER BY   area, month;

mysql> SELECT  area, month, SUM(amount) FROM testa
    -> GROUP BY   area, month ORDER BY   area, month;
+--------+-------+-------------+
| area   | month | SUM(amount) |
+--------+-------+-------------+
| 上海   | 1月   |         199 |
| 上海   | 2月   |         199 |
| 上海   | 3月   |         310 |
| 上海   | 4月   |         125 |
| 北京   | 1月   |         235 |
| 北京   | 2月   |         330 |
| 广州   | 1月   |          75 |
| 广州   | 2月   |          67 |
+--------+-------+-------------+
8 rows in set (0.01 sec)

二.rollup语句

rollup(a)=(a) union all (_ALL)
–rollup(a,b)=(a,b) union all (a) union all (_ALL)
–rollup(a,b,c)=(a,b,c) union all (a,b) union all (a) union all (_ALL)

-- 求每个地区、每月汇总，以及每个地区的汇总，以及总的汇总
SELECT   ifnull (area, '地区合计') area, ifnull(month, '月合计') month, SUM(amount)
FROM   testa
GROUP BY  area, month with rollup;

mysql> SELECT   ifnull (area, '地区合计') area, ifnull(month, '月合计') month, SUM(amount)
    -> FROM   testa
    -> GROUP BY  area, month with rollup;
+--------------+-----------+-------------+
| area         | month     | SUM(amount) |
+--------------+-----------+-------------+
| 上海         | 1月       |         199 |
| 上海         | 2月       |         199 |
| 上海         | 3月       |         310 |
| 上海         | 4月       |         125 |
| 上海         | 月合计    |         833 |
| 北京         | 1月       |         235 |
| 北京         | 2月       |         330 |
| 北京         | 月合计    |         565 |
| 广州         | 1月       |          75 |
| 广州         | 2月       |          67 |
| 广州         | 月合计    |         142 |
| 地区合计     | 月合计    |        1540 |
+--------------+-----------+-------------+
12 rows in set, 2 warnings (0.01 sec)

三.grouping语句

grouping(expr)
– 若是expr的汇总，则返回1，否则返回0

判断是否是area/month列的汇总
SELECT   ifnull(area,'地区合计') area, ifnull(month,'月合计') month, SUM(amount) s_amount,grouping(month) grping1,grouping(area) grping2
FROM   testa
GROUP BY area, month with rollup;

mysql> SELECT   ifnull(area,'地区合计') area, ifnull(month,'月合计') month, SUM(amount) s_amount,grouping(month) grping1,grouping(ar
    -> FROM   testa
    -> GROUP BY area, month with rollup;
+--------------+-----------+----------+---------+---------+
| area         | month     | s_amount | grping1 | grping2 |
+--------------+-----------+----------+---------+---------+
| 上海         | 1月       |      199 |       0 |       0 |
| 上海         | 2月       |      199 |       0 |       0 |
| 上海         | 3月       |      310 |       0 |       0 |
| 上海         | 4月       |      125 |       0 |       0 |
| 上海         | 月合计    |      833 |       1 |       0 |
| 北京         | 1月       |      235 |       0 |       0 |
| 北京         | 2月       |      330 |       0 |       0 |
| 北京         | 月合计    |      565 |       1 |       0 |
| 广州         | 1月       |       75 |       0 |       0 |
| 广州         | 2月       |       67 |       0 |       0 |
| 广州         | 月合计    |      142 |       1 |       0 |
| 地区合计     | 月合计    |     1540 |       1 |       1 |
+--------------+-----------+----------+---------+---------+
12 rows in set, 2 warnings (0.00 sec)