SQL，力扣题目569，员工薪水中位数

一、力扣链接

LeetCode_569

二、题目描述

表: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| company      | varchar |
| salary       | int     |
+--------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行表示公司和一名员工的工资。

编写解决方案，找出每个公司的工资中位数。

以 任意顺序 返回结果表。

三、目标拆解

四、建表语句

Create table If Not Exists Employee (id int, company varchar(255), salary int)
Truncate table Employee
insert into Employee (id, company, salary) values ('1', 'A', '2341')
insert into Employee (id, company, salary) values ('2', 'A', '341')
insert into Employee (id, company, salary) values ('3', 'A', '15')
insert into Employee (id, company, salary) values ('4', 'A', '15314')
insert into Employee (id, company, salary) values ('5', 'A', '451')
insert into Employee (id, company, salary) values ('6', 'A', '513')
insert into Employee (id, company, salary) values ('7', 'B', '15')
insert into Employee (id, company, salary) values ('8', 'B', '13')
insert into Employee (id, company, salary) values ('9', 'B', '1154')
insert into Employee (id, company, salary) values ('10', 'B', '1345')
insert into Employee (id, company, salary) values ('11', 'B', '1221')
insert into Employee (id, company, salary) values ('12', 'B', '234')
insert into Employee (id, company, salary) values ('13', 'C', '2345')
insert into Employee (id, company, salary) values ('14', 'C', '2645')
insert into Employee (id, company, salary) values ('15', 'C', '2645')
insert into Employee (id, company, salary) values ('16', 'C', '2652')
insert into Employee (id, company, salary) values ('17', 'C', '65')

五、过程分析

1、按照薪资排序

2、找出中位数所在行，列出结果字段

六、代码实现

with t1 as(
    select id, company, salary,
           row_number() over(partition by company order by salary, id) rn1,   -- 需要在salary的基础上再根据id排序
           row_number() over(partition by company order by salary desc, id desc) rn2
    from employee
)
select id, company, salary
from t1
where rn1 = rn2 or rn1 = rn2 - 1 or rn2 = rn1 - 1;

七、结果验证

八、小结

1、CTE表达式 + 排名函数

2、中位数字段升序和降序的排名相同或者差值为1