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

mysql count 为null时，显示0

1.使用ifnull

ifnull（字段名，目标值）

?

1 2 3 4 5

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 下执行如下命令

?

1	select use_city, count (*) from data where os= "Windows 2003 Std" group by use_city;

得到的结果为：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

+ -----------+----------+ | 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”是没有记录符合的，怎么让他显示成

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

+ -----------+----------+ | 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的记录也显示出来，做法如下：

?

1 2 3 4 5 6 7

SELECT use_city, COUNT ( CASE WHEN os= 'Windows 2003 Std' THEN 1 ELSE NULL END ) FROM data GROUP BY use_city

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。 

原文链接：https://blog.csdn.net/weixin_37460672/article/details/119273481