LeetCode180-连续出现的数字

glProgramming

于 2023-08-24 19:24:41 发布

阅读量169

点赞数

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/m0_51539432/article/details/132481084

版权

该篇文章介绍了如何使用SQL查询，特别是`row_number()`函数和窗口函数，来解决LeetCode上关于查找数据库中连续出现三次或更多次数的数字的问题。作者详细展示了如何通过分组、计数和窗口排序来实现这一功能。

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

题目

https://leetcode.cn/problems/consecutive-numbers/description/

在这里插入图片描述

# row_number() over([partition by value_expression,...n] order by columnName)
#1.最外层 查结果表 得到 num
#select distinct num as ConsecutiveNums from result;
#2. 至少连续出现三次的数字
#select num2, count(1) from temp_table group by num2,z having count(1)>=3; // 出现三次以上
#// 连续的 重新生成id y, 按 num分组，组内再排序x， y-x 相等表示是连续的一组; 组内排序
#select id, num,  (row_number() over(order by id) -row_number() over(partition by num order by id)) as SerialNumberSubGroup from Logs;
# 整合

select distinct num as ConsecutiveNums from (
select num, count(1)  from (
  select id, num,  (row_number() over(order by id) -row_number() over(partition by num order by id)) as SerialNumberSubGroup from Logs
) sub group by num, SerialNumberSubGroup having count(1)>=3 
) as result;