如何在SQL Server 2017中无CTE实现孤岛间隙的分区排名

可以不用CTE，仅通过窗口函数+子查询的组合就能解决这类孤岛间隙问题，实现对间隔出现的相同item进行正确分区并聚合。

解决方案SQL语句 #

假设原始数据表名为your_table，字段为item、value、ID（ID用于保证数据的顺序，是处理孤岛问题的排序依据）：

SELECT
    item,
    MIN(value) AS MIN,
    MAX(value) AS MAX,
    MIN(ID) AS ID
FROM (
    SELECT
        item,
        value,
        ID,
        -- 生成分组键：连续相同item的差值固定，间隔item的差值不同
        ROW_NUMBER() OVER (ORDER BY ID) - ROW_NUMBER() OVER (PARTITION BY item ORDER BY ID) AS group_key
    FROM your_table
) AS sub_query
GROUP BY item, group_key
ORDER BY ID;

逻辑说明 #

1. 分组键生成：子查询中使用两个ROW_NUMBER()的差值作为group_key：
   • 第一个ROW_NUMBER()按全局ID顺序生成行号；
   • 第二个ROW_NUMBER()按item分区后再按ID生成行号；
   • 连续相同的item会得到相同的group_key，一旦item切换，group_key会发生变化，以此区分不同的孤岛分区。
2. 聚合计算：外层查询按item和group_key分组，计算每组的MIN(value)和MAX(value)，同时用MIN(ID)作为分区的ID，与预期结果完全匹配。
3. 排序输出：最后按ID排序，保证结果顺序符合要求。

替代写法（用DENSE_RANK生成ID） #

如果不需要依赖原始ID生成分区ID，也可以用DENSE_RANK()生成自增的分区ID：

SELECT
    item,
    MIN(value) AS MIN,
    MAX(value) AS MAX,
    DENSE_RANK() OVER (ORDER BY group_key) AS ID
FROM (
    SELECT
        item,
        value,
        ID,
        ROW_NUMBER() OVER (ORDER BY ID) - ROW_NUMBER() OVER (PARTITION BY item ORDER BY ID) AS group_key
    FROM your_table
) AS sub_query
GROUP BY item, group_key
ORDER BY ID;

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