MySQL 使用表的自联结，lag，lead处理连续段问题-526互联

mysql

我有一列，列名为id

大致长这样
2, 3, 5, 6, 7, 8

我需要保留所有所在连续段长度>=3的所有行



# SELECT  Version() # 8.0.33，用户变量编程用不了

WITH Numbered AS (
    SELECT id,
           LAG(id,1) OVER (ORDER BY id) AS prev_id,
           LEAD(id,1) OVER (ORDER BY id) AS next_id
    FROM your_table_name
)

SELECT id 
FROM (
    SELECT id,
           CASE WHEN id - prev_id = 1 AND next_id - id = 1 THEN 1
                WHEN id - prev_id = 1 AND next_id - id != 1 THEN 1
                WHEN id - prev_id != 1 AND next_id - id = 1 THEN 1
                ELSE 0 END AS is_in_continuous_segment
    FROM Numbered
) AS Derived
WHERE is_in_continuous_segment = 1;

问题

MySQL