sql语句 case when or 自己写的sqlselect dateformat(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 loginFROM (select distinct imei,dateformat(c
2020-03-02
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') then imei end）是计数reqtype='login' 和reqtype='register'的数量；
COUNT(distinct imei), 是当reqtype='login' 和reqtype='register'时去重计数imei的数量．
count（case when (reqtype='login' or reqtype='register') then imei end）是计数reqtype='login' 和reqtype='register'的数量；
COUNT(distinct imei), 是当reqtype='login' 和reqtype='register'时去重计数imei的数量．