2021-06-02

找到连续区间的开始和结束数字

需求：编写一个 SQL 查询得到 Logs 表中的连续区间的开始数字和结束数字。

展示效果：

+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+

建表语句：

Create table If Not Exists 69_Logs (log_id int);
Truncate table 69_Logs;
insert into 69_Logs (log_id) values ('1');
insert into 69_Logs (log_id) values ('2');
insert into 69_Logs (log_id) values ('3');
insert into 69_Logs (log_id) values ('7');
insert into 69_Logs (log_id) values ('8');
insert into 69_Logs (log_id) values ('10');

最终sql：

--开窗函数，row_number() over（）按照 1 2 3 4顺序写，diff相同则说明连续
SELECT
    MIN(log_id) start_id,
    MAX(log_id) end_id
FROM
    (SELECT
        log_id, 
        (log_id - row_number() OVER(ORDER BY log_id)) as diff
     FROM 
        69_Logs ) t
GROUP BY diff