题解 | #每月各旬有多少人练题#

select dt_range,count(distinct device_id)cnt from(
select 
distinct device_id,
(case 
when day(event_date) between 1 and 9 
     then concat(DATE_FORMAT(event_date,'%Y年%m月'),'上旬')
when day(event_date) between 10 and 19 
     then concat(DATE_FORMAT(event_date,'%Y年%m月'),'中旬')
when day(event_date) >=20
     then concat(DATE_FORMAT(event_date,'%Y年%m月'),'下旬')
else null end)dt_range
from question_practice_detail
having dt_range is not null)aa
group by dt_range
order by SUBSTRING(dt_range,6,2)  desc,cnt desc

每月有多少人练题所以要去掉重复人员id

concat(字段,'中旬') 字段内容拼接字符

DATE_FORMAT(event_date,'%Y年%m月') 时间转中文字符

SUBSTRING(dt_range,6,2) 截取字符串索引从1开始截取两个