题目：

Employee 表包含所有员工。Employee 表有三列：员工Id，公司名和薪水。

image

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

image

按 company 分组排序，记为 rk 计算各 company 的记录数除以2，记为 cnt 连接结果 找出符合中位数要求的记录 有 2 个要点：分组排序通过两个变量完成，注意 @com 的更新顺序要在 @rk 之后 按示例，若记录数为奇数，取一条，否则取两条，如记录数为7，则第4名是中位数， 记录数为6，则第3，4名是中位数。由于 cnt = 计数除以2，则对应序号可直接用 in (cnt+0.5,cnt+1,cnt) 来表达

参考答案：

数据库版本：Server version: 8.0.20 MySQL Community Server - GPL

建表语句

create table dailytest_20200717(
    id int,
    company varchar(20),
    salary int
);

数据准备

insert into dailytest_20200717 values (1,'A',2341);
insert into dailytest_20200717 values (2,'A',341);
insert into dailytest_20200717 values (3,'A',15);
insert into dailytest_20200717 values (4,'A',15314);
insert into dailytest_20200717 values (5,'A',451);
insert into dailytest_20200717 values (6,'A',513);
insert into dailytest_20200717 values (7,'B',15);
insert into dailytest_20200717 values (8,'B',13);
insert into dailytest_20200717 values (9,'B',1154);
insert into dailytest_20200717 values (10,'B',1345);
insert into dailytest_20200717 values (11,'B',1221);
insert into dailytest_20200717 values (12,'B',234);
insert into dailytest_20200717 values (13,'C',2345);
insert into dailytest_20200717 values (14,'C',2645);
insert into dailytest_20200717 values (15,'C',2645);
insert into dailytest_20200717 values (16,'C',2652);
insert into dailytest_20200717 values (17,'C',65);

查询逻辑

select
    D.id,
    D.company,
    D.salary
from
(
#     获取数据表中的中位数记录数据
    select
    B.company,
    B.mid_num
from
(
#     先对数据表按company分组salay排序获取顺序cn字段，再对最大salary的记录取余判断如果为奇数，则为中位数记录
select
       A.company,
       case when mod(max(A.cn)/2,2) = 1 then max(A.cn)/2
           when mod(max(A.cn)/2,2) <> 1 then null end as mid_num
from
(
select
       id,
       company,
       salary,
       row_number() over (partition by company order by salary) as cn
from dailytest_20200717)A
group by A.company
union all
#     先对数据表按company分组salay排序获取顺序cn字段，再对最大salary的记录取余判断如果为偶数，则为中位数记录
select
       A.company,
       case when mod(max(A.cn)/2,2) = 1 then max(A.cn)/2+1
           when mod(max(A.cn)/2,2) <> 1 then ceil(max(A.cn)/2) end as mid_num
from
(
select
       id,
       company,
       salary,
       row_number() over (partition by company order by salary) as cn
from dailytest_20200717)A
group by A.company)B
where B.mid_num is not null) C
inner join
(
select
       id,
       company,
       salary,
       row_number() over (partition by company order by salary) as cn
from dailytest_20200717) D
on C.company = D.company
       and
   C.mid_num = D.cn;

附：
题目来源：https://mp.weixin.qq.com/s/FVFtNuRyEvx67b1DFHQSnA