MySQL：用UNION实现带优先级与限制的无重复排序查询

原始数据表 #

ID|isYN|insertDate
1 |Y    |2012-12-16
2 |Y    |2012-12-17
3 |Y    |2012-12-18
4 |N    |2012-12-30
5 |N    |2012-09-20
6 |N    |2012-11-10

需求说明 #

1. 前两条数据必须是isYN='Y'的记录，且按insertDate降序排列
2. 剩余所有数据（包括剩下的isYN='Y'记录）统一按insertDate降序排列
3. 结果不能有重复数据

预期结果 #

ID|isYN|insertDate
3 |Y    |2012-12-18
2 |Y    |2012-12-17
4 |N    |2012-12-30
1 |Y    |2012-12-16
6 |N    |2012-11-10
5 |N    |2012-09-20

问题分析 #

用UNION加优先级列的方式容易出现重复，原因是UNION的两个查询范围可能重叠（比如部分isYN='Y'的记录被同时包含在两个子查询里），如果没加DISTINCT就会产生重复数据，加了DISTINCT又可能打乱排序逻辑。

解决方案：窗口函数+自定义排序字段 #

不需要拆分查询，直接通过窗口函数标记优先级，一次扫描全表即可实现需求，避免重复：

SELECT ID, isYN, insertDate
FROM (
    SELECT 
        ID, 
        isYN, 
        insertDate,
        -- 给isYN='Y'的记录按日期降序编号
        ROW_NUMBER() OVER (PARTITION BY isYN ORDER BY insertDate DESC) AS y_rn
    FROM your_table -- 替换成你的表名
) t
ORDER BY 
    -- 第一优先级：isYN='Y'且排名前2的记录
    CASE WHEN isYN = 'Y' AND y_rn <= 2 THEN 1 ELSE 2 END,
    -- 前2条Y记录内部按编号升序（即日期降序）
    CASE WHEN isYN = 'Y' AND y_rn <= 2 THEN y_rn ELSE NULL END,
    -- 剩余所有记录按日期降序
    insertDate DESC;

逻辑说明 #

1. 子查询中用ROW_NUMBER()给isYN='Y'的记录按insertDate降序生成序号y_rn，最新的Y记录序号为1，次新为2，以此类推
2. 外层排序时：
   • 先把y_rn<=2的Y记录排在最前面（优先级1），内部按y_rn升序保证日期从新到旧
   • 剩下的所有记录（优先级2）统一按insertDate降序排列，完全符合需求

内容的提问来源于stack exchange，提问作者mimimi