如何查询表中连续出现至少三次相同值的记录？

我创建了如下Test3表：

创建表及插入数据的SQL语句：

Create Table Test3
(
    id int,
    val int
)

Insert Into Test3 Values (1,0),(2,1),(3,0),(4,1),(5,1),(6,1),(7,0)

我想要的输出为：

需求说明：需要找出val列中存在连续三个相同值的所有对应id。如果第7行的val也为1，那么它的id也应该出现在结果中。我尝试用自连接实现但未得到预期结果，该如何解决？

下面提供几种可行的实现方式，优先推荐窗口函数方案，逻辑清晰且适用性更强。

方法一：用LAG/LEAD窗口函数直接判断连续值 #

通过LAG和LEAD函数获取当前行前后的val值，直接判断当前行是否属于连续三个相同值的组：

WITH cte AS (
    SELECT 
        id,
        val,
        LAG(val, 1) OVER (ORDER BY id) AS prev_val,
        LAG(val, 2) OVER (ORDER BY id) AS prev_prev_val,
        LEAD(val, 1) OVER (ORDER BY id) AS next_val,
        LEAD(val, 2) OVER (ORDER BY id) AS next_next_val
    FROM Test3
)
SELECT DISTINCT id
FROM cte
WHERE 
    -- 当前行是连续三个的第一个
    (val = prev_val AND val = prev_prev_val)
    -- 当前行是连续三个的中间
    OR (val = prev_val AND val = next_val)
    -- 当前行是连续三个的最后一个
    OR (val = next_val AND val = next_next_val)
ORDER BY id;

方法二：先分组再筛选连续组 #

先给每个连续相同val的序列分配唯一组号，统计每组的行数后，筛选出行数≥3的组的所有id：

WITH cte AS (
    SELECT 
        id,
        val,
        -- 当前行val和前一行不同时，组号+1，以此标记连续相同val的组
        SUM(CASE WHEN val = LAG(val) OVER (ORDER BY id) THEN 0 ELSE 1 END) 
            OVER (ORDER BY id) AS group_id
    FROM Test3
),
group_counts AS (
    SELECT group_id, COUNT(*) AS cnt
    FROM cte
    GROUP BY group_id
    HAVING COUNT(*) >= 3
)
SELECT c.id
FROM cte c
JOIN group_counts gc ON c.group_id = gc.group_id
ORDER BY c.id;

方法三：修正自连接的写法 #

如果一定要用自连接，需要覆盖连续三个id的所有情况，通过UNION收集所有符合条件的id：

SELECT DISTINCT t1.id
FROM Test3 t1
JOIN Test3 t2 ON t2.id = t1.id + 1 AND t2.val = t1.val
JOIN Test3 t3 ON t3.id = t1.id + 2 AND t3.val = t1.val

UNION

SELECT DISTINCT t2.id
FROM Test3 t1
JOIN Test3 t2 ON t2.id = t1.id + 1 AND t2.val = t1.val
JOIN Test3 t3 ON t3.id = t1.id + 2 AND t3.val = t1.val

UNION

SELECT DISTINCT t3.id
FROM Test3 t1
JOIN Test3 t2 ON t2.id = t1.id + 1 AND t2.val = t1.val
JOIN Test3 t3 ON t3.id = t1.id + 2 AND t3.val = t1.val
ORDER BY id;

注意：这种方法仅适用于id是连续递增的情况，如果id存在断层，需要用窗口函数生成连续序号替代原id进行判断。

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