【sql】连续出现至少3次的数 Consecutive Numbers

为什么80%的码农都做不了架构师？>>>   

问题：

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

解决：

① 找出连续出现3次以上的数的值。由于需要找三次相同数字，所以我们需要建立三个表的实例，我们可以用l1分别和l2, l3内交，l1和l2的Id下一个位置比，l1和l3的下两个位置比，然后将Num都相同的数字返回即可。1822 ms

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

② 直接在三个表的实例中查找，然后把四个条件限定上，就可以返回正确结果了。 2803 ms

SELECT DISTINCT l1.Num 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;

③ 用到了变量count和pre，分别初始化为0和-1。2438 ms

SELECT DISTINCT Num ConsecutiveNums FROM (
    SELECT Num,@count := IF(@pre = Num,@count + 1,1) As n,@pre := Num
    FROM Logs,(SELECT @count := 0,@pre := -1) As init
    ) As t WHERE t.n >= 3;

转载于:https://my.oschina.net/liyurong/blog/1570710