569. 员工薪水中位数

表: Employee

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

 

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

以 任意顺序 返回结果表。

查询结果格式如下所示。

 

示例 1:

输入: 
Employee 表:
+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 1  | A       | 2341   |
| 2  | A       | 341    |
| 3  | A       | 15     |
| 4  | A       | 15314  |
| 5  | A       | 451    |
| 6  | A       | 513    |
| 7  | B       | 15     |
| 8  | B       | 13     |
| 9  | B       | 1154   |
| 10 | B       | 1345   |
| 11 | B       | 1221   |
| 12 | B       | 234    |
| 13 | C       | 2345   |
| 14 | C       | 2645   |
| 15 | C       | 2645   |
| 16 | C       | 2652   |
| 17 | C       | 65     |
+----+---------+--------+
输出: 
+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 5  | A       | 451    |
| 6  | A       | 513    |
| 12 | B       | 234    |
| 9  | B       | 1154   |
| 14 | C       | 2645   |
+----+---------+--------+

 

进阶: 你能在不使用任何内置函数或窗口函数的情况下解决它吗?

select b.id,b.company,b.salary
-- 3. 连接结果
from (
    -- 1. 按 company 分组排序，记为 `rk`
    select id,company,salary,
    case @com when company then @rk:=@rk+1 else @rk:=1 end rk,
    @com:=company
    from employee,(select @rk:=0, @com:='') a
    order by company,salary) b
left join 
    (-- 2. 计算各 company 的记录数除以2，记为 `cnt`
    select company,count(1)/2 cnt from employee group by company) c
on b.company=c.company
-- 4. 找出符合中位数要求的记录
where b.rk in (cnt+0.5,cnt+1,cnt);
-- 觉得有用请不吝点个赞哟

select Id,Company,Salary
from
(
select Id,Company,Salary, 
row_number()over(partition by Company order by Salary)as ranking,
count(Id) over(partition by Company)as cnt
from Employee
)a
where ranking>=cnt/2 and ranking<=cnt/2+1