180. Consecutive Numbers

qq_34505594

于 2019-08-19 22:03:20 发布

阅读量79

点赞数

分类专栏： LeetCode 数据库

本文链接：https://blog.csdn.net/qq_34505594/article/details/99768176

版权

LeetCode 同时被 2 个专栏收录

20 篇文章 0 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

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

方法一：

select distinct A.Num  ConsecutiveNums from 
Logs A left join Logs B on A.Id = B.Id-1
left join Logs C on A.Id = C.Id-2
where A.Num = B.Num and A.Num = C.Num

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

方法二：官方解答

tips:当id有跳跃的时候不适用

例：

1 9

2 6

4 6

5 8

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

方法三：

#Author:kent-huang

select DISTINCT num FROM
(select num,
	case 
		when @record = num then @count:=@count+1
		when @record <> @record:=num then @count:=1 end as n
    from 
	    Logs ,(select @count:=0,@record:=(SELECT num from Logs limit 0,1)) r
) a
where a.n>=3

方法四：

#Runtime: 299 ms, faster than 94.39% of MySQL online submissions for Consecutive Numbers.
#Memory Usage: N/A
#Author:luokiss39


select distinct Num as ConsecutiveNums
from Logs
where (Id + 1, Num) in (select * from Logs) and (Id + 2, Num) in (select * from Logs)

So many solutions on the website, please see https://leetcode.com/problems/consecutive-numbers/discuss?currentPage=1&orderBy=most_votes&query=