SQL连续数值分组：将Table1转换为Table2的实现方法求助

示例数据与预期结果 #

Table1 原始数据：

id	Value
1	0
2	1
3	2
4	0
5	1
6	2
7	3

Table2 目标结果：

min_id	max_id	min_value	max_value
1	3	0	2
4	7	0	3

实现方案（支持窗口函数的数据库：MySQL 8+、PostgreSQL、SQL Server等） #

核心思路是通过窗口函数标记分组：以Value=0为分组起点，计算每行之前出现的0的次数作为分组ID，再按分组ID聚合。

WITH grouped_data AS (
    SELECT 
        id,
        Value,
        -- 累计统计当前行及之前的0的数量，作为分组标识
        SUM(CASE WHEN Value = 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS group_id
    FROM Table1
)
SELECT
    MIN(id) AS min_id,
    MAX(id) AS max_id,
    MIN(Value) AS min_value,
    MAX(Value) AS max_value
FROM grouped_data
GROUP BY group_id
ORDER BY group_id;

兼容旧版MySQL（无窗口函数）的方案 #

使用用户变量生成分组ID：

SELECT
    MIN(id) AS min_id,
    MAX(id) AS max_id,
    MIN(Value) AS min_value,
    MAX(Value) AS max_value
FROM (
    SELECT
        id,
        Value,
        @group_id := @group_id + (CASE WHEN Value = 0 THEN 1 ELSE 0 END) AS group_id
    FROM Table1, (SELECT @group_id := 0) AS init
    ORDER BY id
) AS grouped_data
GROUP BY group_id
ORDER BY group_id;

逻辑说明 #

1. 分组标记：无论是窗口函数还是用户变量，核心都是给每个"0起始的连续递增序列"分配唯一的group_id——每遇到一个Value=0，分组ID就递增。
2. 聚合计算：按group_id分组后，直接用聚合函数MIN()、MAX()提取每组的边界值即可。

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