LeetCode MySQL 180. 连续出现的数字（cast）

Michael阿明

发布于 2021-02-19 10:43:49

5410

发布于 2021-02-19 10:43:49

文章被收录于专栏：Michael阿明学习之路

文章目录

- 1. 题目
- 2. 解题

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. 解题

SQL中的cast()函数

select num, row_number() over(partition by Num) rnk
from 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) rnk
from Logs

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

# Write your MySQL query statement below
select distinct num ConsecutiveNums
from
(
    select num, Id-cast(row_number() over(partition by Num) as signed) rnk
    from Logs
) t
group by num, rnk
having count(*) >= 3

或者

# Write your MySQL query statement below
select distinct a.num ConsecutiveNums
from Logs a, Logs b, Logs c
where   a.Id = b.Id+1
    and b.Id = c.Id+1
    and a.Num = b.Num
    and b.Num = c.Num

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020/08/01 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql