如何简化获取匹配ID或分组首个行号记录的TSQL语句

简化思路 #

直接将业务优先级规则嵌入窗口函数的排序逻辑中，无需额外统计分组匹配状态、关联查询以及结果合并，可大幅简化语句同时提升执行效率。

简化后完整代码 #

WITH cte AS
(
    SELECT 'A' AS Id1, '1' AS Id2, 'DSFSF' AS _detail
    UNION ALL
    SELECT 'A' AS Id1, '2' AS Id2, 'ASDF' AS _detail
    UNION ALL
    SELECT 'A' AS Id1, 'A' AS Id2, '434242' AS _detail
    UNION ALL
    SELECT 'B' AS Id1, '1' AS Id2, 'gsreew' AS _detail
    UNION ALL
    SELECT 'B' AS Id1, '2' AS Id2, 'werw' AS _detail
    UNION ALL
    SELECT 'B' AS Id1, '3' AS Id2, '67575' AS _detail
),
rank_cte AS (
    SELECT *,
        ROW_NUMBER() OVER(
            PARTITION BY Id1 
            ORDER BY 
                CASE WHEN Id1 = Id2 THEN 0 ELSE 1 END ASC,
                Id2 ASC
        ) AS rn
    FROM cte
)
SELECT Id1, Id2, _detail
FROM rank_cte
WHERE rn = 1;

逻辑说明 #

窗口函数的排序规则完全匹配需求：

1. 优先把分组内Id1=Id2的记录排在最前，存在该类记录时会直接排在分组第一位被取出
2. 不存在Id1=Id2的记录时，自动按Id2升序排序，取第一条记录
3. 每个分组仅取排序后的第一行即可覆盖所有场景，和原语句执行结果完全一致。

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