【Database】【LeetCode】180. Consecutive Numbers

SQL Schema >

Create table If Not Exists Logs (Id int, Num int);
Truncate table Logs;
insert into Logs (Id, Num) values ('1', '1');
insert into Logs (Id, Num) values ('2', '1');
insert into Logs (Id, Num) values ('3', '1');
insert into Logs (Id, Num) values ('4', '2');
insert into Logs (Id, Num) values ('5', '1');
insert into Logs (Id, Num) values ('6', '2');
insert into Logs (Id, Num) values ('7', '2');

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

题解：

 

将这个表错位连接三次即可得到连续的三个值

SQL:

SELECT 
    DISTINCT l1.Num AS ConsecutiveNums
FROM 
    Logs AS l1 
JOIN 
    Logs AS l2
ON 
    l1.Id = l2.Id-1
JOIN 
    Logs AS l3
ON 
    l2.Id = l3.Id-1
WHERE 
    l1.Num=l2.Num AND l2.Num = l3.Num;