mysql count 为null时，显示0的问题

SELECT a.*,IFNULL(r.count,0) from (SELECT act_id,poster_id,type,style_type,status,status_time,title,content,images,start_time,end_time,district_id,address,lon_map,lat_map,person_num,person_name,person_phone,person_wx_id,reward_min,reward_max,cost_type,meal_svc,taxi_svc,hair_length,hair_handle,remark,is_over,create_time,note 
FROM activity WHERE poster_id = 3055808629673784641 ORDER BY create_time DESC limit 0,20) a LEFT JOIN (SELECT count(1) count,act_id
                            FROM activity_user u
                            where  u.user_status in ('YBM', 'YTG', 'YQR')
                            GROUP BY u.act_id) r on a.act_id = r.act_id
2.运行结果
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)
GROUP BY
use_city
以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。 
您可能感兴趣的文章:
MySQL中count()查询的性能梳理
mysql数据库之count()函数和sum()函数用法及区别说明
mysql一条sql查出多个条件不同的sum或count问题
MySQL count(*),count(id),count(1),count(字段)区别
mysql count()函数不计算null和空值问题
2023-07-07
2023-07-07
2023-07-07
2023-08-08
2023-08-08
2023-08-08
2023-08-08
2023-08-08
2021-11-19
2021-11-09
2021-11-05
2021-10-26
电脑版 - 返回首页
2006-2023 脚本之家 JB51.Net , All Rights Reserved.
苏ICP备14036222号