使用 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)