SQL查询按sn排序后相邻重复的多行数据问题

我有一个颜色表t1，按sn排序后的数据如下：

select * from t1 order by sn
+---+-------+
|sn |color  |
+---+-------+
|25 |Brown  |
|29 |Brown  |
|33 |Blue   |
|37 |Red    |
|42 |Green  |
|46 |Yellow |
|49 |Red    |
|53 |Red    |
|57 |Red    |
|61 |Blue   |
|65 |Red    |
|69 |Pink   |
+---+-------+

需要找出按sn排序后，相邻2行及以上颜色相同的记录，期望结果如下：

|25 |Brown  |
|29 |Brown  |

|49 |Red    |
|53 |Red    |
|57 |Red    |

我尝试过使用SQL的lag()函数，语句如下：

select sn, color, lag(color) over(order by sn) prev_color from t1 

但该方法仅能识别2条相邻的重复行，无法扩展为通用解决方案，请问该如何解决此问题？

解决方案 #

可以通过分组标记连续重复的颜色组实现通用解决方案，核心逻辑是给每一段连续相同的颜色分配唯一组ID，再筛选出组内记录数≥2的组：

1. 生成连续重复组ID：
   利用两次row_number()的差值标记连续组——全局按sn排序的行号，减去按color分组后按sn排序的行号，连续相同的颜色会得到相同的差值（即组ID）。

2. 筛选有效组并提取记录：
   统计每个组的记录数量，保留数量≥2的组，再关联回原表取出对应记录。

完整SQL语句如下：

WITH color_groups AS (
    SELECT 
        sn, 
        color,
        -- 生成连续重复组的唯一ID
        ROW_NUMBER() OVER(ORDER BY sn) - 
        ROW_NUMBER() OVER(PARTITION BY color ORDER BY sn) AS group_id
    FROM t1
),
valid_groups AS (
    SELECT group_id
    FROM color_groups
    GROUP BY group_id, color
    HAVING COUNT(*) >= 2
)
SELECT sn, color
FROM color_groups
WHERE group_id IN (SELECT group_id FROM valid_groups)
ORDER BY sn;

该方法支持任意长度的连续重复记录，执行后即可得到符合要求的结果。

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