Leetcode: Consecutive Numbers

最新推荐文章于 2020-04-21 22:38:05 发布

ojshilu

最新推荐文章于 2020-04-21 22:38:05 发布

阅读量805

点赞数

分类专栏：数据库 LeetCode

本文链接：https://blog.csdn.net/ojshilu/article/details/44316175

版权

LeetCode 同时被 2 个专栏收录

151 篇文章 0 订阅

订阅专栏

数据库

7 篇文章 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.

Required:
appear 3 times consecutively.

Code 1: For every row, check its next row and then the next.

select a.Num
from Logs a
where exists
(
select 1 from Logs b where a.Id+2 = b.Id and a.Num = b.Num
)
and exists
(
select 1 from Logs b where a.Id+1 = b.Id and a.Num = b.Num
)

O(n^3) time complexity.

Code 2:

select distinct a.Num
from Logs a, Logs b, Logs c
where
a.Id = b.Id+1 and a.Id = c.Id+2 and a.Num = b.Num and a.Num = c.Num

SQL is just programming language. First you need to be quite familar with the API that you can use. Then you have to think method or algorithem to solve problems.