sql语句 case when or - 微思作业本

问题描述：

sql语句 case when or
自己写的sql
select date_format(createDate,'%Y-%m-%d') as createDate,
count(case when reqtype='runUp' then imei end) as runUp,
count(case when (reqtype='login' or reqtype='register') then imei end) as login
FROM (select distinct imei,date_format(createDate,'%Y-%m-%d') as createDate,reqtype
from stat_user_loginreq_201312 where 1=1 and DATE_FORMAT(createDate,'%Y-%m-%d') = '2013-12-01'
) AS g group by DATE_FORMAT(createDate,'%Y-%m-%d') order by createDate
查出来的count（case when (reqtype='login' or reqtype='register') then imei end）的值和
select COUNT(distinct imei),date_format(createDate,'%Y-%m-%d') from stat_user_loginreq_201312 where 1=1
and (reqtype='login' or reqtype='register')
and DATE_FORMAT(createDate,'%Y-%m-%d') = '2013-12-01'
group by DATE_FORMAT(createDate,'%Y-%m-%d') order by createDate
查出来的不一样

1个回答分类：综合 2014-11-06

问题解答：

我来补答

count（case when (reqtype='login' or reqtype='register') then imei end）是计数reqtype='login' 和reqtype='register'的数量；
COUNT(distinct imei),　是当reqtype='login' 和reqtype='register'时去重计数imei的数量．
再问：那我怎么写才能实现统计reqtype的值是login或者register的count（imei）值呢 count（case when (reqtype='login' or reqtype='register') then imei end）我这样查出来的只是reqtype='login' 的count值就这点不明白
再答： count是计数，如果是：""实现统计reqtype的值是login或者register的count（imei）值"",也就是说当实现统计reqtype的值是login或者register有多少个　IMEI；此时与IMEI值本身有任何关系．可以使用：sum(case when reqtype in ('login','register') then 1 else 0 end) 如果你是统计reqtype的值是login或者register时IMEI的值，此时与IMEI值有关，可以使用： sum(case when reqtype in ('login','register') then imei else 0 end)就行啦．

展开全文阅读