赞
踩
569. 员工薪水中位数
写一个SQL查询,找出每个公司的工资中位数。
以 任意顺序 返回结果表。
查询结果格式如下所示。
SQL:方法一
select id, company, salary from (
select
id, company, salary,
row_number() over(partition by company order by salary) as rk,
count(id) over(partition by company) as total
from employee
) as t
where t.rk in (floor((total + 1) / 2), floor((total + 2) / 2));
解析
SELECT Id, Company, Salary
FROM Employee
WHERE Id in (
SELECT e1.Id
FROM Employee e1
JOIN Employee e2
ON e1.Company = e2.Company
GROUP BY e1.Id
HAVING SUM(CASE WHEN e1.Salary >= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2
AND SUM(CASE WHEN e1.Salary <= e2.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2
)
GROUP BY Company, Salary
ORDER BY Company
步骤:
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。