SQL - 2

2. 分数排名

需求:通过SQL查询实现分数排名,如果两个分数相同,则两个分数排名相同,要求名次之间不能有间隔。

展示效果:

Score Rank
4.00 1
4.00 1
3.85 2
3.65 3
3.65 3
3.50 4

建表语句:

Create table If Not Exists Scores (Id int, Score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (Id, Score) values (1, 3.5);
insert into Scores (Id, Score) values (2, 3.65);
insert into Scores (Id, Score) values (3, 4.0);
insert into Scores (Id, Score) values (4, 3.85);
insert into Scores (Id, Score) values (5, 4.0);
insert into Scores (Id, Score) values (6, 3.65);

方法1:

select
	Score,
	dense_rank() over(order by Score desc ) `rank`
from Scores;

#思路:通过dense_rank()  函数按照分数降序排名即可,如果要求有间隔用rank().

方法2:

select 
	Score,
	(select 
    	count(distinct b.Score)
    from Scores b
    where 
    	b.Score >= a.Score ) as `rank`
from Scores 
order by Score desc;

#思路:通过比自己大的分数的个数确定自己的排名,要求无间隔需要将重复的分数使用distinct去重。

3.连续出现的数字

需求:通过SQL,查找所有至少连续出现三次的数字。

展示效果:

ConsecutiveNums
1

建表语句:

Create table If Not Exists Logs (Id int, Num int);
Truncate table Logs;
insert into Logs (Id, Num) values (1, 1);
insert into Logs (Id, Num) values (2, 1);
insert into Logs (Id, Num) values (3, 1);
insert into Logs (Id, Num) values (4, 2);
insert into Logs (Id, Num) values (5, 1);
insert into Logs (Id, Num) values (6, 2);
insert into Logs (Id, Num) values (7, 2);

方法1:

select
	l1.num
from 
	logs l1,
	logs l2,
	logs l3
where l1.id = l2.id -1 and l1.num = l2.num
	and l2.id = l3.id -1 and l2.num =l3.num;

方法2:

select 
	l1.num
from 
	logs l1
left join 
	logs l2 
on l1.id =l2.id-1 
left join 
	logs l3 
on l2.id = l3.id -1
where l1.num = l2.num and l2.num = l3.num ;

方法3:

#1.
select 
	num,
	lead(num,1,null) over(order by id) n2,
	lead(num,2,null) over(order by id) n3 
from logs

#2.
select
	distinct num ConsecutiveNums
from (
    select 
        num,
        lead(num,1,null) over(order by id) n2,
        lead(num,2,null) over(order by id) n3 
    from logs
) t1 
where num = n2 and num = n3;
作者:yuexiuping
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.
posted @ 2021-06-08 22:55  yuexiuping  阅读(8)  评论(0编辑  收藏  举报