题目描述：
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
