实际业务中会遇到将数值按照某字段分组聚合后,取名为中文,那么想要按照我们需要的顺序排序,就会使用到case when将想排列到前的记录分组字段名取值为1。
举例
计算一个超期的天数,正常分组及返回结果。
select
over_sign,
count(*)
from
(select
case when timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))<=5 then '超期0-5天'
when timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))>5
and timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))<=10 then '超期6-10天'
when timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))>10
and timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))<=15 then '超期11-15天'
when timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))>15
and timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))<=20 then '超期16-20天'
when timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))>20 then '超期21天及以上'
else null
end as over_sign
from
dm.order_delivery_cycle_end_all
)t
where over_sign is not null
group by over_sign
修改结果
select
over_sign,
count(*)
from
(select
case when timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))<=5 then '超期0-5天'
when timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))>5
and timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))<=10 then '超期6-10天'
when timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))>10
and timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))<=15 then '超期11-15天'
when timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))>15
and timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))<=20 then '超期16-20天'
when timestampdiff(day,dd_out_arrive_date,date_sub(curdate(),interval 1 day))>20 then '超期21天及以上'
else null
end as over_sign
from
dm.order_delivery_cycle_end_all
)t
where over_sign is not null
group by over_sign
order by
case over_sign when '超期0-5天' then 1
when '超期6-10天' then 2
when '超期11-15天' then 3
when '超期16-20天' then 4
when '超期21天及以上' then 5
end