SQL对分组聚合后的结果按照字符字段排序

最新推荐文章于 2024-08-29 17:59:52 发布

Forge_ahead

最新推荐文章于 2024-08-29 17:59:52 发布

阅读量169

点赞数

分类专栏： work_efficiency 文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_50646402/article/details/131824263

版权

work_efficiency 专栏收录该内容

45 篇文章 2 订阅

订阅专栏

在实际业务中，经常需要按特定顺序对数据进行分组和聚合。此SQL查询示例展示了如何计算订单的超期天数，并将其分为不同的区间。通过CASEWHEN语句创建了描述性标签（如超期0-5天），然后利用同样的逻辑在ORDERBY子句中定义排序顺序，确保结果按预期的超期天数区间从短至长排列。

摘要由CSDN通过智能技术生成

实际业务中会遇到将数值按照某字段分组聚合后，取名为中文，那么想要按照我们需要的顺序排序，就会使用到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