mysql让count为0的记录也显示出来
在mysql 下执行如下命令:
select use_city,count(*) from data where os="Windows 2003 Std" group by use_city;
得到的结果为:
+-----------+----------+
| use_city | count(*) |
+-----------+----------+
| Beijing | 2 |
| Chengdu | 2 |
| Chongqing | 1 |
| Dalian | 2 |
| Fuzhou | 2 |
| Guangzhou | 2 |
| Hangzhou | 2 |
| Nanjing | 2 |
| Shanghai | 21 |
| Shenyang | 5 |
| Wuhan | 1 |
+-----------+----------+
11 rows in set (0.01 sec)
共有11条记录,问题是其中有一个城市“Tianjing”是没有记录符合的,怎么让他显示成
+-----------+----------+
| use_city | count(*) |
+-----------+----------+
| Beijing | 2 |
| Chengdu | 2 |
| Chongqing | 1 |
| Dalian | 2 |
| Fuzhou | 2 |
| Guangzhou | 2 |
| Hangzhou | 2 |
| Nanjing | 2 |
| Shanghai | 21 |
| Tianjing | 0 |
| Shenyang | 5 |
| Wuhan | 1 |
+-----------+----------+
让它count为0的记录也显示出来,做法如下:
SELECT
use_city,
COUNT(CASE WHEN os='Windows 2003 Std' THEN 1 ELSE NULL END)
FROM
data
GROUP BY
use_city