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