569. 员工薪水中位数

SQL架构

表: Employee

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

写一个SQL查询，找出每个公司的工资中位数。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 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
e1.e1id id,e1.e1co company,e1.e1s salary

from
(select
id as e1id,row_number() over( partition by company order by salary ) as e1o,salary as e1s,company as e1co
from
Employee) as e1 left join 
(select
-- 工资中位数所在的位置 偶数时  [e2l,e2r] 奇数数时 [e2l,null]
company as co,if(mod(count(salary),2)=0,round (count(salary)/2),round((count(salary)+1)/2)) as e2l,if(mod(count(salary),2)=0,round (count(salary)/2+1),null) as e2r

from
Employee
group by company) as e2
on e1.e1co= e2.co
where e1.e1o=e2.e2l or e1.e1o=e2r  -- 根据公司关联后 把 标号e1o 与[e2l,e2r] 匹配起来 能匹配的就是结果

法二:

    select
        id , 
        company , 
        salary
    from
        (select
            id , 
            company , 
            salary ,
            row_number() over(partition by company order by salary) as rn,
            count(salary) over(partition by company) as cnt
        from Employee) as a
    where rn in (cnt/2 , cnt/2+1 , cnt/2+0.5)