如何在SQL Server中实现复杂ORDER BY排序需求
2026-8-14
需求说明
需要对customer_updates表执行以下排序操作：
- 先按每个
Cust_Id对应的**最新Last_updated_date**降序排列各组 - 组内再按
Last_updated_date降序排列
常规排序后的表
| Update_ID | Cust_Id | Field_updated | Last_updated_date | Updated_by |
|---|---|---|---|---|
| 1 | 1223 | Name | 2021-11-01 12:23 | Frodo Baggins |
| 2 | 9999 | address | 2021-12-02 19:23 | Legolas |
| 3 | 2200 | phone | 2021-12-03 23:00 | Bilbo Baggins |
| 4 | 2200 | Name | 2022-01-04 02:23 | Bilbo Baggins |
| 5 | 9999 | phone | 2022-02-05 12:23 | Golum |
| 6 | 9999 | address | 2022-03-06 20:00 | Sauron |
| 7 | 1223 | address | 2022-04-07 01:24 | Gandalf |
| 8 | 2200 | 2022-05-08 12:50 | Some Urkai | |
| 9 | 3412 | email, phone | 2022-06-08 08:45 | Golum |
| 10 | 1223 | address | 2022-07-10 00:23 | Pippin |
| 11 | 3412 | email, address | 2022-09-22 16:48 | Gandalf |
预期结果
| Update_ID | Cust_Id | Field_updated | Last_updated_date | Updated_by |
|---|---|---|---|---|
| 11 | 3412 | email, address | 2022-09-22 16:48 | Gandalf |
| 9 | 3412 | email, phone | 2022-06-08 08:45 | Golum |
| 10 | 1223 | address | 2022-07-10 00:23 | Pippin |
| 7 | 1223 | address | 2022-04-07 01:24 | Gandalf |
| 1 | 1223 | Name | 2021-11-01 12:23 | Frodo Baggins |
| 8 | 2200 | 2022-05-08 12:50 | Some Urkai | |
| 4 | 2200 | Name | 2022-01-04 02:23 | Bilbo Baggins |
| 3 | 2200 | phone | 2021-12-03 23:00 | Bilbo Baggins |
| 6 | 9999 | address | 2022-03-06 20:00 | Sauron |
| 5 | 9999 | phone | 2022-02-05 12:23 | Golum |
| 2 | 9999 | address | 2021-12-02 19:23 | Legolas |
解决方案
可以使用窗口函数MAX()计算每个Cust_Id对应的最新更新日期，再基于该值和原日期完成排序：
SELECT Update_ID, Cust_Id, Field_updated, Last_updated_date, Updated_by FROM customer_updates ORDER BY -- 按客户组的最新更新日期降序排列组 MAX(Last_updated_date) OVER (PARTITION BY Cust_Id) DESC, -- 组内按更新日期降序排列 Last_updated_date DESC;
思路说明
MAX(Last_updated_date) OVER (PARTITION BY Cust_Id)：为每条记录生成其所属Cust_Id的最新更新日期- 优先按该全局最大值降序排序，确保更新时间最近的客户组排在最前
- 组内再按
Last_updated_date降序，保证组内记录从新到旧排列
内容的提问来源于stack exchange，提问作者Lazytitan
相关产品推荐
相关产品推荐
