链接:https://leetcode-cn.com/problems/consecutive-numbers/
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs
表, 1
是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
方法一: 三表连接
要注意用distinct 对num去重。
sql语句:
select
distinct l1.Num ConsecutiveNums
from
Logs l1,
Logs l2,
Logs l3
where
l2.Id-l1.Id=1
and l3.Id-l2.Id=1
and l1.Num = l2.Num
and l2.Num = l3.Num
方法二: 使用lead函数
lead(col,n,DEFAULT) over([ partition_by_clause ] order_by_clause)
用于统计窗口内往上第n行值
lag(col,n,DEFAULT) over([ partition_by_clause ] order_by_clause)
用于统计窗口内往下第n行值
第一个参数为 列名
第二个参数为往下第n行(可选,默认为1)
第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
over中的排序和分组可省略
思路:将num分别上移1和2位,如果它们相等,就是3个连续相等的
滑动如下图:
sql语句
select distinct num as ConsecutiveNums from
(
select Num,lead(Num,1)over()as num1,lead(Num,2)over()as num2
from Logs
) as c
where c.num = c.num1 and c.num1 = c.num2;
方法三: 使用 row_number
参考这位大哥的:https://leetcode-cn.com/problems/consecutive-numbers/solution/sql-server-jie-fa-by-neilsons/
语法格式:row_number() over(partition by 分组列 order by 排序列 desc)
partition by 可以不写,但order by必须写,over()里头的分组以和排序的执行晚于 where 、group by和
order by 的执行。
思路:这个比较厉害,使用row_number,根据id排序的 - (根据id排序,根据num分组)等于一个值。如果这个值相等,就说明它们是连续的,统计重复次数大于等于3的num并去重,就是想要的结果。
sql语句
select distinct Num ConsecutiveNums from (
select Num,count(1) countNum from
(select Id, Num,
row_number() over (order by Id) -
row_number() over (partition by Num order by Id) as SunGroup
from Logs ) as Sub
group by Num,SunGroup having count(1)>=3
) as result;