SQL实现取中位数

场景：Employee 表包含所有员工，该表有三列：员工Id、公司名、薪水，写SQL查询来查找每个公司的薪水中位数，有两种方法实现

方法1：正序+倒序排名

-- 如果员工数为基数：rnk1 = rnk2
-- 如果员工数为偶数：rnk1 = rnk2-1 或者 rnk2+1
select Id, Company, Salary
from
(
    select Id, Company, Salary,
        row_number() over (partition by company order by salary, id) as rnk1, 
        row_number() over (partition by company order by salary desc, id desc) as rnk2
    from employee
) a
where rnk1 in (rnk2, rnk2 - 1, rnk2 + 1)

方法2：总数/2

-- 如果员工数为基数：rnk = cnt/2+0.5
-- 如果员工数为偶数：rnk = cnt/2 和 cnt/2+1
select Id,Company,Salary from 
(
select 
    Id,Company,Salary,
    row_number() over(partition by Company order by Salary) as rnk,
    count(1) over(partition by Company) as cnt 
from Employee 
) t 
where rnk in (cnt/2, cnt/2+1, cnt/2+0.5)