本文共 1197 字，大约阅读时间需要 3 分钟。

1. 题目

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+| Id | Num |+----+-----+| 1  |  1  || 2  |  1  || 3  |  1  || 4  |  2  || 5  |  1  || 6  |  2  || 7  |  2  |+----+-----+例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。+-----------------+| ConsecutiveNums |+-----------------+| 1               |+-----------------+

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/consecutive-numbers

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

2. 解题

select num, row_number() over(partition by Num) rnkfrom Logs

{
  "headers": ["num", "rnk"], "values": [[1, 1], [1, 2], [1, 3], [1, 4], [2, 1], [2, 2], [2, 3]]}

select num, Id-cast(row_number() over(partition by Num) as signed) rnkfrom Logs

{
  "headers": ["num", "rnk"], "values": [[1, 0], [1, 0], [1, 0], [1, 1], [2, 3], [2, 4], [2, 4]]}

# Write your MySQL query statement belowselect distinct num ConsecutiveNumsfrom(    select num, Id-cast(row_number() over(partition by Num) as signed) rnk    from Logs) tgroup by num, rnkhaving count(*) >= 3

或者

# Write your MySQL query statement belowselect distinct a.num ConsecutiveNumsfrom Logs a, Logs b, Logs cwhere   a.Id = b.Id+1    and b.Id = c.Id+1    and a.Num = b.Num    and b.Num = c.Num

我的CSDN

长按或扫码关注我的公众号（Michael阿明），一起加油、一起学习进步！

转载地址：https://michael.blog.csdn.net/article/details/107735590 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！