LeetCode: 180. Consecutive Numbers

题目描述

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 | +-----------------+

解题思路

将三个 logs 表链接，然后筛选出符合要求的内容。

AC 代码

SELECT DISTINCT logs1.num AS ConsecutiveNums 
FROM logs AS logs1, logs AS logs2, logs As logs3
WHERE logs1.Id = logs2.Id+1 AND logs2.Id = logs3.Id+1 AND logs1.Num = logs2.Num AND logs2.Num = logs3.Num