Employee
表包含所有员工。Employee
表有三列:员工Id,公司名和薪水。
+-----+------------+--------+ |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 | +-----+------------+--------+
请编写SQL查询来查找每个公司的薪水中位数。挑战点:你是否可以在不使用任何内置的SQL函数的情况下解决此问题。
+-----+------------+--------+ |Id | Company | Salary | +-----+------------+--------+ |5 | A | 451 | |6 | A | 513 | |12 | B | 234 | |9 | B | 1154 | |14 | C | 2645 | +-----+------------+--------+
select
company_rownumber.Id as Id,
company_rownumber.Company as Company,
company_rownumber.Salary as Salary
from
#第一部分算row_number,每个公司内部的排序
(select
Id,
Company,
Salary,
@company_no:=case when @company_name = company then @company_no+1 else 1 end as company_no,
@company_name:=company
from
(select id,company,salary from employee,(select @company_no:=0,@company_name:="") b) c
order by
Company,
Salary) company_rownumber
join
#第二部分算每个公司总共有多少个
(select
info.Id,
info.Company,
info.Salary,
cnt
from
(select
Id,
Company,
Salary
from
Employee) info
join
(select
Company,
count(1) as cnt
from
Employee
group by
Company) company_cnt
on
info.Company = company_cnt.Company) company_group_cnt
on
company_rownumber.Id = company_group_cnt.Id
#限定每个公司的row_number在总数的一半的区间里
where
company_no >= cnt/2
and company_no <= cnt/2+1