【LeetCode】569. Median Employee Salary 员工薪水中位数

第一次做到SQL的题。

window function

select
    id,
    company,
    salary
from (
    select
        *,
        (count(salary) over (partition by company)) / 2.0 as cnt,
        row_number() over (partition by company order by salary) as rk
    from Employee
) t
where rk between cnt and cnt+1

cross join

这样每一行都会和其它所有行交叉出一行来，对每个(id, company, salary)，计算出同company里排在它后面的个数-同company里排在它前面的个数，这个值在[-1, 1]区间内时，该salary就是中位数。

# Write your MySQL query statement below
select
    t1.id,
    t1.company,
    t1.salary
from Employee t1
join Employee t2
on t1.company = t2.company
group by t1.id, t1.company, t1.salary
having abs(sum(case
    when (t1.salary < t2.salary) or (t1.salary = t2.salary and t1.id < t2.id) then 1
    when (t1.salary > t2.salary) or (t1.salary = t2.salary and t1.id > t2.id) then -1
    else 0 end    
)) <= 1