Leetcode: Consecutive Numbers

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

原创最新推荐文章于 2020-04-21 22:38:05 发布 · 859 阅读

0 ·

CC 4.0 BY-SA版权

LeetCode 同时被 2 个专栏收录

151 篇文章

订阅专栏

数据库

7 篇文章

订阅专栏

本文介绍了一种SQL查询方法，用于找出在表格中连续出现至少三次的数字。通过两个不同的实现方式，一种是使用子查询检查每行及其后两行，另一种是通过连接三次相同ID偏移的表格来实现。这两种方法虽各有优劣，但都能有效地解决该问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.