Leetcode NO.180. Consecutive Numbers

180. Consecutive Numbers

原题目链接:180. Consecutive Numbers s

写一条SQL查询所有连续出现至少3次的数字。

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

比如,在上面的Logs表当中,1是唯一的连续出现至少3次的数字。

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

Solution

方法一:采用DISTINCTWHERE语句[Accepted]

Algorithm

连续出现则意味着NumId相互挨在一起。因为题目是要求至少连续出现3次的数字,我们可以用3个Logs表的别名,然后检查3个连续的数字是否相同。

相应的SQL代码如下:

1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;

结果如下:






























IdNumIdNumIdNum
112131
>Note: The first two columns are from l1, then the next two are from l2, and the last two are from l3.

注意上面表中的三组数据分别来自l1,l2和l3。

然后,我们可以从上面三组数据当中选择任意的一组Num作为目标数据。我们还需要增加一个DISTINCT关键字,因为如果一个数字出现超过3次这里会重复该数字。

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;