mysql:联合重用子查询

Question

有一张 table :

ID        City      

1          Toronto
2          Toronto
3          Toronto
4          Toronto
5          NY
6          NY

如何获取下表:

City    Total

NULL    6
Toronto 4
NY      2

我打算使用union:

(select city, count(*) as total from tbl group by city)
union
(select null, count(*) as total from tbl)

是否可以使用类似重用子查询的方法:

(select city, count(*) as total from tbl group by city) tmp
union
(select null, count(*) as tmp from tbl)

或者sql已经优化

(select city, count(*) as total from tbl group by city)
union
(select null, count(*) as total from tbl)

?

Answer 1

至少在 MySQL 5.5 和更新版本中，您可以使用 WITH ROLLUP无需额外查询即可获得分组元素总和的修饰符；

SELECT city, COUNT(*) AS total FROM tbl GROUP BY city WITH ROLLUP

city      total
---------------
NY        2
Toronto   4
(null)    6

An SQLfiddle to test with .