select count(distinct device_id) cnt,
case when day(event_date) between 01 and 09 then concat(year(event_date),'年',mouth(event_date),'月','上旬')
when day(event_date) between 10 and 19 then concat(year(event_date),'年',mouth(event_date),'月','中旬')
when day(event_date)>20then concat(year(event_date),'年',mouth(event_date),'月','下旬') end dt_range
from question_practice_detail
group by dt_range
order by mouth(event_date) desc,cnt desc
这个是我的bug代码,我用时间函数然后链接
select
(
concat(
concat(substr(event_date,1,4),'年'),
concat(substr(event_date,6,2),'月'),
(case
when day(event_date) between 1 and 9 then '上旬'
when day(event_date) between 10 and 19 then '中旬'
when day(event_date) >=20 then '下旬'
end))
)as dt_range,
count(distinct device_id) as cnt
from question_practice_detail
group by dt_range
order by substr(dt_range,6,2) desc, cnt desc
这是答案,用substri提取字符串
但我觉得用时间提取函数也ok吖。