MySQL获取分组数据中每组的中位数

案例：员工薪水中位数

查询结果：

Id	Company	Salary
5	A	451
6	A	513
12	B	234
9	B	1154
14	C	2645

SELECT 
  b.id,
  b.company,
  b.salary,
  b.rk 
FROM
  (SELECT     
    id,
    company,
    salary,
    @com,
    # 当@com列的值等于company列的时候，让@rk列自增,不相等时，变成1
    CASE
      @com 
      WHEN company 
      THEN @rk := @rk + 1 #用变量实现行号时，必须用:=，用=无效果
      ELSE @rk := 1 
    END rk,     # 行号
    # 将@com赋值成company行
    @com := company 
  FROM  
    # 让employee表和自定义的@rk 和 @com两列进行内联接
    employee,
    (SELECT 
      @rk := 0,
      @com := '') a 
  ORDER BY company,
    salary) b 
  LEFT JOIN #需要添加列，这列是每组company中的中间值
    (SELECT 
      company,
      COUNT(1) / 2 cnt 
    FROM
      employee 
    GROUP BY company) c 
    ON c.company = b.company 
WHERE 
 #中位数的可能范围在中间值+0.5，中间值，中间值+1
  b.rk IN (c.cnt + 0.5, c.cnt, c.cnt + 1)

employee表的数据

Create table If Not Exists Employee (Id int, Company varchar(255), Salary int);

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