如何调整SQL查询：筛选Documents表时优先保留Pending Effective行

需求回顾 #

从Document表中筛选状态为Effective或Pending Effective的文档，需遵循以下规则：

• 同一文档编号（Number）同时存在两种状态时，仅返回Pending Effective行，排除Effective行
• 文档编号仅存在Effective行时，返回该行

原查询会返回同一编号的两行数据，以下是两种可行的调整方案：

方案一：使用窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

通过ROW_NUMBER()窗口函数给每个文档编号分组内的行按状态优先级排序，优先保留Pending Effective行，再筛选每组的第一行即可。

SELECT Name, Number, Revision, Status
FROM (
    SELECT 
        Name,
        Number,
        Revision,
        Status,
        -- 按文档编号分组，给状态设置排序优先级：Pending Effective排1，Effective排2
        ROW_NUMBER() OVER (
            PARTITION BY Number 
            ORDER BY CASE Status 
                WHEN 'Pending Effective' THEN 1 
                WHEN 'Effective' THEN 2 
            END
        ) AS rn
    FROM Document
    WHERE Status IN ('Pending Effective', 'Effective')
      AND Number = 'D001234' -- 若需查询所有符合条件的文档，可删除此条件
) AS ranked
WHERE rn = 1;

方案二：使用NOT EXISTS子查询（兼容旧版本数据库） #

逻辑为：保留所有Pending Effective行；仅当同一文档编号不存在Pending Effective行时，才保留其Effective行。

SELECT Name, Number, Revision, Status
FROM Document d
WHERE 
    (Status = 'Pending Effective' 
     OR (Status = 'Effective' 
         AND NOT EXISTS (
             SELECT 1 
             FROM Document 
             WHERE Number = d.Number 
               AND Status = 'Pending Effective'
         )
        ))
    AND Number = 'D001234'; -- 若需查询所有符合条件的文档，可删除此条件

示例验证 #

示例数据 #

期望结果 #

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