1.数据准备和需求
数据准备：
create table employee ( id int auto_increment primary key comment '编号', company varchar(255) comment '公司', salary int comment '工资' ); 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);
计算每个公司工资的中位数，结果如下：
+----+---------+--------+ | id | company | salary | +----+---------+--------+ | 6 | A | 513 | | 5 | A | 451 | | 9 | B | 1154 | | 12 | B | 234 | | 14 | C | 2645 | +----+---------+--------+
2.结果实现
思路1：N为奇数，中位数排序编号是(N+1)/2；N为偶数，中位数排序编号是N/2和N/2+1，不管奇偶数就是(N+1)/2向下取整和(N+2)/2向下取整。
select id, company, salary from (select id, company, salary, cast(row_number() over (partition by company order by salary,id) as signed) asc_salary, count(1) over (partition by company) total_num from employee) t where asc_salary in ((total_num+1)/2,total_num/2,total_num/2+1) -- where asc_salary in (floor((total_num + 1)/2), floor((total_num + 2)/2)) ;
思路2：N为奇数，正序排序编号和逆序排序编号相等(相差0)，N为偶数，正序排序编号和逆序排序编号互换(相差1)；
select id, company, salary from (select id, company, salary, cast(row_number() over (partition by company order by salary,id) as signed) asc_salary, cast(row_number() over (partition by company order by salary desc,id desc) as signed) desc_salary from employee) t where asc_salary = desc_salary or abs(asc_salary-desc_salary) = 1;
思路3：不管奇数和偶数，正序排序编号、逆序排序编号大于等于N/2；
select id, company, salary from (select id, company, salary, cast(row_number() over (partition by company order by salary,id) as signed) asc_salary, cast(row_number() over (partition by company order by salary desc,id desc) as signed) desc_salary, count(1) over (partition by company) total_num from employee) t where asc_salary >= total_num/2 and desc_salary >= total_num/2;
思路4：不管数组长度是奇是偶，也不管元素是否唯一，中位数出现的频率一定大于等于 大于它的数与小于它的数的绝对值之差。
select min(id) id a.company, a.salary from employee as a, employee as b where a.company = b.company group by a.company, a.salary having sum(a.salary = b.salary) >= abs(sum(sign(a.salary - b.salary)))
