需求：请编写SQL查询来查找每个公司的薪水中位数。挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题。
展示效果：
| Id | Company | Salary |
|---|---|---|
| 5 | A | 451 |
| 6 | A | 513 |
| 12 | B | 234 |
| 9 | B | 1154 |
| 14 | C | 2645 |
1 Create table If Not Exists Employee (Id int, Company varchar(255), Salary int); 2 3 insert into Employee (Id, Company, Salary) values (1, 'A', 2341); 4 insert into Employee (Id, Company, Salary) values (2, 'A', 341); 5 insert into Employee (Id, Company, Salary) values (3, 'A', 15); 6 insert into Employee (Id, Company, Salary) values (4, 'A', 15314); 7 insert into Employee (Id, Company, Salary) values (5, 'A', 451); 8 insert into Employee (Id, Company, Salary) values (6, 'A', 513); 9 insert into Employee (Id, Company, Salary) values (7, 'B', 15); 10 insert into Employee (Id, Company, Salary) values (8, 'B', 13); 11 insert into Employee (Id, Company, Salary) values (9, 'B', 1154); 12 insert into Employee (Id, Company, Salary) values (10, 'B', 1345); 13 insert into Employee (Id, Company, Salary) values (11, 'B', 1221); 14 insert into Employee (Id, Company, Salary) values (12, 'B', 234); 15 insert into Employee (Id, Company, Salary) values (13, 'C', 2345); 16 insert into Employee (Id, Company, Salary) values (14, 'C', 2645); 17 insert into Employee (Id, Company, Salary) values (15, 'C', 2645); 18 insert into Employee (Id, Company, Salary) values (16, 'C', 2652); 19 insert into Employee (Id, Company, Salary) values (17, 'C', 65);
最终SQL:
1 select 2 b.id, 3 b.company, 4 b.salary 5 from 6 (select 7 id, 8 company, 9 salary, 10 case @com when company then @rk:=@rk+1 else @rk:=1 end rk, 11 @com:=company 12 from 13 employee, 14 (select @rk:=0, @com:='') a 15 order by 16 company,salary 17 ) b 18 left join 19 (select 20 company, 21 count(1)/2 cnt 22 from 23 employee 24 group by company 25 ) c 26 on 27 b.company=c.company 28 where 29 b.rk in (cnt+0.5,cnt+1,cnt);