SQL18 查找当前薪水排名第二多的员工，不使用order by

本文最后更新于：2022年4月9日 中午


题目描述

查找当前薪水(to_date=’9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`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`));

输入描述

无

输出描述

emp_no salary last_name first_name
10009 94409 Peac Sumant

题解

1
2
3
4
5
6
7
8
9
10
11
12
select e.emp_no, s.salary, e.last_name, e.first_name from employees as e join salaries as s on e.emp_no=s.emp_no and s.to_date='9999-01-01'
and s.salary=(select max(salary) from salaries 
              where salary <(select max(salary) from salaries
                            where to_date='9999-01-01')
              and to_date='9999-01-01')
或
select e.emp_no, s.salary, e.last_name, e.first_name from employees as e join salaries as s 
on e.emp_no=s.emp_no and s.to_date='9999-01-01'
and s.salary=(select s1.salary from salaries s1 join salaries as s2 on s1.salary<=s2.salary
              and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
              group by s1.salary having count(distinct s2.salary)=2
             )

备注：

  1. 方法一为多层select嵌套与max()函数结合

  2. 方法二使用自连接查询，去重之后的数量就是对应的名次

    表自连接以后:

    s1 s2
    100 100
    98 98
    98 98
    95 95

    当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2

    s1 s2
    100 100
    98 100
    98
    98
    95 100
    98
    98
    95

    对s2进行去重统计数量, 就是s1对应的排名