SQL练习 2022/7/2

180. 连续出现的数字

题干
代码思路

1. 多表连接

select distinct l1.Num as ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where l1.Id+1=l2.id and l2.id+1=l3.id 
and l1.Num =l2.Num and l2.Num=l3.Num
1
2
3
4

2. 窗口函数
   目前没看懂~~~

select 
    distinct num as 'ConsecutiveNums'
from (
    select
        num, id - cast(rank() over(partition by num order by id) as signed) as 'g' 
    from Logs
) as t
group by num, g
having count(1) >= 3
1
2
3
4
5
6
7
8
9

3. 用开窗做， 核心是 使用 dense_rank() 对nums 和 id 进行排序, 得到排名列rk, 如果连续, 则 rk 列 与 id 列的差应该为一样的值. 之后再用一次group by统计数量即可. 需要注意的是 rk 为无符号数, 需要做一次转化, 否则会出现错误. sql 如下:

with t1 as
 ( select 
   num, 
   id - cast(dense_rank() over (order by num,id) as signed Integer) tmp
   from logs 
) 
select 
distinct num ConsecutiveNums
 from t1 
group by tmp, num 
having count(1) > 2;
1
2
3
4
5
6
7
8
9
10
11

使用 lead 开窗 的核心为 对 num 进行分组 并 对num进行分组并对 组内的 id 进行排序, 去排序后移两位的id, 如果其等于2, 则表示连续.sql如下:

with tmp as (
    select num,
           lead(id, 2) over (partition by num order by id ) - id sub
    from logs
)
select distinct num ConsecutiveNums from tmp where sub = 2 ;

1
2
3
4
5
6
7
8

emmmm开窗还是不懂啊

178. 分数排名

题干
代码思路

1. dense_rank的窗口函数
   窗口函数用法：<窗口函数> OVER ( [PARTITION BY <列清单> ] ORDER BY <排序用列清单> ）
   DENSE_RANK():
   这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
   例如，有3条排在第1位时，排序为：1，1，1，2·····

select score ,dense_rank() over(order by score desc) as rankfrom Scores order by score desc
1

2. 统计比每一个成绩大的不同成绩数量。就可以统计出排名了

select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC

1
2
3
4
5