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
查出来的不一样
count（case when (reqtype='login' or reqtype='register') thenimei 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') thenimei 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)就行啦．