自连接-->排序问题

题目一：按成绩排名

create Table `scores` (

`id` int(2) not null,

`score` decimal(6,2) not null

                      );

 insert into scores(id,score) values

 (1,3.50),(2,3.65),(3,4.00),(4,3.85),(5,4.00),(6,3.65)

 

 

法一：自连接查询

# 将表a中的Score和rank组合起来形成select的输出

select a.Score as Score, # 查出所有的score

(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as 'rank' # 查询出排名，用count函数计算出b.Score >= a.Score的个数来计算排名

from Scores a

order by a.Score DESC # 按降序排序

 

 

法二：窗口函数

select score,

dense_rank() over (order by score desc) `Rank`

from scores

注意：在mysql8.0以前版本，不支持开窗函数，会报错

 

方法三：子查询

select t1.id,t1.score,

(select count(distinct t2.score)

from scores t2

where t1.score <=t2.score) rank

from scores t1

order by  t1.score desc

注意：子查询中可以使用父查询中的参数

 

题目二：

有一个passing表，主键为id，对数量进行排序

select

    p1.id,

    p1.number,

    count(distinct p2.number) as t_rank

from

    passing_number as p1,

    passing_number as p2

where

    p1.number <= p2.number

group by

    p1.id

order by

    t_rank asc,

p1.id asc

 

题目三：对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

CREATE TABLE `salaries` (

`emp_no` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));

 

方法一：

select s1.emp_no, s1.salary, count(distinct s2.salary) rank

from salaries s1

join salaries s2

on s1.salary <= s2.salary

and s1.to_date = '9999-01-01'

and s2.to_date = '9999-01-01'

group by s1.emp_no, s1.salary   

order by rank, s1.emp_no

 

 

 

方法二：

select s1.emp_no, s1.salary, (

    select count(distinct s2.salary)

    from salaries s2

    where s2.to_date = '9999-01-01'

    and s1.salary <= s2.salary

)  rank

from salaries s1

where s1.to_date = '9999-01-01'

order by rank, s1.emp_no

 

方法三：

select s1.emp_no, s1.salary, dense_rank() over (order by s1.salary desc) as rank

from salaries s1

where s1.to_date = '9999-01-01'

order by rank, s1.emp_no

补充：

分数	rank	dense_rank	row_number
10	1	1	1
9	2	2	2
9	2	2	3
7	4	3	4

 

参考：https://blog.csdn.net/weixin_38292570/article/details/107977617