1285. Find the Start and End Number of Continuous Ranges----判断连续数字

Table: Logs

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| log_id        | int     |
+---------------+---------+
id is the primary key for this table.
Each row of this table contains the ID in a log Table.

Since some IDs have been removed from Logs. Write an SQL query to find the start and end number of continuous ranges in table Logs.

Order the result table by start_id.

The query result format is in the following example:

Logs table:
+------------+
| log_id     |
+------------+
| 1          |
| 2          |
| 3          |
| 7          |
| 8          |
| 10         |
+------------+

Result table:
+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+
The result table should contain all ranges in table Logs.
From 1 to 3 is contained in the table.
From 4 to 6 is missing in the table
From 7 to 8 is contained in the table.
Number 9 is missing in the table.
Number 10 is contained in the table.

解法一：利用序号和id之间的差异，如果某段记录是连续数字，那么（log_id - rnk）是一个固定数值。

因此，利用（log_id - rnk）分组，并取得每组中的start_id，end_id，即可实现题目要求

SELECT MIN(log_id) AS start_id, MAX(log_id) AS end_id
FROM
(SELECT log_id, 
ROW_NUMBER() OVER (ORDER BY log_id) AS rnk
FROM LOGS) t
GROUP BY log_id - rnk
ORDER BY start_id

解法二：

1、要获取start_id，通过join得知start_id对应的L2.log_id IS NULL

SELECT L1.log_id AS start_id,
 ROW_NUMBER() OVER(ORDER BY L1.log_id ASC) AS RN
 FROM Logs L1 
 LEFT JOIN Logs L2 ON L1.log_id = L2.log_id + 1
 WHERE L2.log_id IS NULL

2、要获取end_id，通过join得知end_id对应的L2.log_id IS NULL

SELECT L1.log_id AS end_id,
 ROW_NUMBER() OVER(ORDER BY L1.log_id ASC) AS RN
 FROM Logs L1 LEFT JOIN Logs L2 ON L1.log_id = L2.log_id - 1
 WHERE L2.log_id IS NULL

3、综合join，最后得到每段连续记录中的start_id和end_id

WITH CTE1 AS
(SELECT L1.log_id AS start_id,
 ROW_NUMBER() OVER(ORDER BY L1.log_id ASC) AS RN
 FROM Logs L1 
 LEFT JOIN Logs L2 ON L1.log_id = L2.log_id + 1
 WHERE L2.log_id IS NULL),
CTE2 AS
(SELECT L1.log_id AS end_id,
 ROW_NUMBER() OVER(ORDER BY L1.log_id ASC) AS RN
 FROM Logs L1 LEFT JOIN Logs L2 ON L1.log_id = L2.log_id - 1
 WHERE L2.log_id IS NULL)
SELECT start_id, end_id
FROM CTE1 INNER JOIN CTE2 
ON CTE1.RN = CTE2.RN

参考同类型题目1225. Report Contiguous Dates