SQL Server中按优先级筛选DocumentType指定值的查询实现

需求说明 #

需要按以下优先级筛选数据：

• 优先选取所有DocumentType = 'I797'的行
• 如果不存在'I797'类型，则选取所有DocumentType = 'I94'的行
• 若上述两种类型都不存在，再选取其他所有类型的行

示例数据 #

实现方案 #

方案一：使用窗口函数（推荐） #

利用窗口函数标记优先级，筛选出当前CaseId下优先级最高的所有行：

WITH RankedDocs AS (
    SELECT 
        *,
        -- 给不同类型分配优先级：I797优先级最高（1），I94次之（2），其他为3
        CASE DocumentType 
            WHEN 'I797' THEN 1 
            WHEN 'I94' THEN 2 
            ELSE 3 
        END AS TypePriority,
        -- 获取当前CaseId下的最高优先级（最小数值）
        MIN(CASE DocumentType 
                WHEN 'I797' THEN 1 
                WHEN 'I94' THEN 2 
                ELSE 3 
            END) OVER (PARTITION BY CaseId) AS HighestPriority
    FROM YourTableName
)
SELECT CaseId, DocId, DocumentTypeId, DocumentType, ExpirationDate
FROM RankedDocs
WHERE TypePriority = HighestPriority;

逻辑说明：

1. 用CASE语句给每个文档类型赋予优先级数值，数值越小优先级越高
2. 通过窗口函数MIN() OVER (PARTITION BY CaseId)拿到当前CaseId下的最高优先级值
3. 最后筛选出所有优先级等于最高优先级的行，即可得到符合要求的结果

方案二：使用EXISTS条件判断 #

通过嵌套的EXISTS逐步验证高优先级类型是否存在，进而筛选数据：

SELECT *
FROM YourTableName t
WHERE 
    -- 优先取I797类型
    DocumentType = 'I797'
    OR 
    -- 若当前CaseId没有I797，取I94类型
    (
        DocumentType = 'I94' 
        AND NOT EXISTS (SELECT 1 FROM YourTableName WHERE CaseId = t.CaseId AND DocumentType = 'I797')
    )
    OR 
    -- 若前两种类型都不存在，取其他所有类型
    (
        DocumentType NOT IN ('I797', 'I94') 
        AND NOT EXISTS (SELECT 1 FROM YourTableName WHERE CaseId = t.CaseId AND DocumentType IN ('I797', 'I94'))
    );

逻辑说明：

1. 先判断当前CaseId是否存在I797，存在则只返回该类型的行
2. 若不存在I797，再判断是否存在I94，存在则返回该类型的行
3. 若前两种都不存在，最后返回所有其他类型的行

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