基于索引和ID有条件关联表获取父DocumentID的SQL实现问题

核心逻辑说明 #

我们只需按IdentifierBySet分组统计每组最大的IndexBySet，再按规则匹配目标行即可：

• 若组内最大IndexBySet>1，取IndexBySet等于最大值减1的行
• 若组内最大IndexBySet=1，取IndexBySet等于1的行

实现代码 #

方案1：窗口函数实现（推荐，适配SQL Server 2012及以上版本，写法简洁性能优） #

WITH GroupMaxStat AS (
    SELECT
        *,
        -- 按IdentifierBySet分组计算每组最大的IndexBySet
        MAX(IndexBySet) OVER (PARTITION BY IdentifierBySet) AS MaxIndexInGroup
    FROM @tbl
)
SELECT
    IdentifierBySet,
    IndexBySet,
    DocumentID
FROM GroupMaxStat
WHERE IndexBySet = CASE WHEN MaxIndexInGroup > 1 THEN MaxIndexInGroup - 1 ELSE 1 END

方案2：分组关联实现（兼容所有SQL版本，适配不支持窗口函数的低版本环境） #

SELECT
    t.IdentifierBySet,
    t.IndexBySet,
    t.DocumentID
FROM @tbl t
INNER JOIN (
    -- 先统计每个IdentifierBySet对应的最大IndexBySet
    SELECT IdentifierBySet, MAX(IndexBySet) AS MaxIndexInGroup
    FROM @tbl
    GROUP BY IdentifierBySet
) stat ON t.IdentifierBySet = stat.IdentifierBySet
WHERE t.IndexBySet = CASE WHEN stat.MaxIndexInGroup > 1 THEN stat.MaxIndexInGroup - 1 ELSE 1 END

以上两种方案运行你提供的测试数据，输出结果和预期完全一致。

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