如何从含重复记录的请假申请表中查询去重记录且优先返回status非空记录

实现方案 #

核心思路是使用ROW_NUMBER()窗口函数对相同user_id、from_date、to_date、reason的记录按优先级排序：已审批（status IS NOT NULL）的记录优先级高于待审批记录，每个分组仅取排序第一的记录即可。

WITH ranked_leaves AS (
    SELECT 
        a.leave_app_id,
        a.user_id,
        a.from_date,
        a.to_date,
        a.reason,
        a.status,
        a.approved_by,
        -- 同分组内排序规则：已审批优先，同优先级可按业务需求调整取最新/最早的申请
        ROW_NUMBER() OVER (
            PARTITION BY a.user_id, a.from_date, a.to_date, a.reason 
            ORDER BY CASE WHEN a.status IS NOT NULL THEN 1 ELSE 2 END, a.leave_app_id DESC
        ) AS rn
    FROM tbl_leave_applications a
    INNER JOIN tbl_users u ON u.user_id = a.user_id
)
SELECT 
    leave_app_id,
    user_id,
    from_date,
    to_date,
    reason,
    status,
    CONCAT_WS(' ', u.first_name, u.last_name) AS approved_by
FROM ranked_leaves r
LEFT JOIN tbl_users u ON u.user_id = r.approved_by
WHERE rn = 1;

如果你使用的数据库版本不支持CTE语法，也可以使用子查询写法，效果一致：

SELECT 
    leave_app_id,
    user_id,
    from_date,
    to_date,
    reason,
    status,
    CONCAT_WS(' ', u2.first_name, u2.last_name) AS approved_by
FROM (
    SELECT 
        a.leave_app_id,
        a.user_id,
        a.from_date,
        a.to_date,
        a.reason,
        a.status,
        a.approved_by,
        ROW_NUMBER() OVER (
            PARTITION BY a.user_id, a.from_date, a.to_date, a.reason 
            ORDER BY CASE WHEN a.status IS NOT NULL THEN 1 ELSE 2 END, a.leave_app_id DESC
        ) AS rn
    FROM tbl_leave_applications a
    INNER JOIN tbl_users u1 ON u1.user_id = a.user_id
) t
LEFT JOIN tbl_users u2 ON u2.user_id = t.approved_by
WHERE rn = 1;

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