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;