group by columns with rollup

使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

其中记录 NULL 表示所有人的登录次数。

mysql> select * from tb1;
+----+----------------------------------+----------------------------------+
| id | key1                             | key2                             |
+----+----------------------------------+----------------------------------+
|  1 | 111111111111111111 | 2222222222222222 |
+----+----------------------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from tb_ctf where key1='' or ''='' group by key2 with rollup;
+----+----------------------------------+----------------------------------+
| id | key1                             | key2                             |
+----+----------------------------------+----------------------------------+
|  1 | 111111111111111111 | 2222222222222222 |
|  1 | 1111111111111111111 | NULL                             |
+----+----------------------------------+----------------------------------+
2 rows in set (0.00 sec)

mysql> select * from tb_ctf where key1='' or ''='' group by key2;
+----+----------------------------------+----------------------------------+
| id | key1                             | key2                             |
+----+----------------------------------+----------------------------------+
|  1 | 111111111111111111 | 22222222222222222|
+----+----------------------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from tb_ctf where key1='' or ''='' group by key1 with rollup;
+----+----------------------------------+----------------------------------+
| id | key1                             | key2                             |
+----+----------------------------------+----------------------------------+
|  1 | 111111111111111111 | 2222222222222222 |
|  1 | NULL                             | 222222222222222 |
+----+----------------------------------+----------------------------------+
2 rows in set (0.00 sec)