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
|
SELECTuse_city,COUNT(CASE WHEN os='Windows 2003 Std' THEN 1 ELSE NULL END)FROMdataGROUP BYuse_city |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/weixin_37460672/article/details/119273481
