大数据—Mysql练习题13- 员工薪水中位数

需求：请编写SQL查询来查找每个公司的薪水中位数。挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题。

展示效果：

IdCompanySalary
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);

 

posted @ 2020-08-19 21:29  浪子逆行  阅读(183)  评论(0)    收藏  举报