leetcode569 SQL中位数问题

2018年8月26日 2点热度 0条评论 来源: 上进的菜鸟
drop table employees;
Create table employees(Id int,Company varchar(2),salary int);
insert into employees values(1,'A',2341);
insert into employees values(2,'A',341);
insert into employees values(3,'A',15);
insert into employees values(4,'A',15314);
insert into employees values(5,'A',451);
insert into employees values(6,'A',513);
insert into employees values(7,'B',15);
insert into employees values(8,'B',13);
insert into employees values(9,'B',1154);
insert into employees values(10,'B',1345);
insert into employees values(11,'B',1221);
insert into employees values(12,'B',234);
insert into employees values(13,'C',2345);
insert into employees values(14,'C',2645);
insert into employees values(15,'C',2645);
insert into employees values(16,'C',2652);
insert into employees values(17,'C',65);
select * from employees;

 

一开始是这样写的

SELECT * FROM
(SELECT e1.* ,(SELECT COUNT(salary)+1 FROM employees e2 WHERE e2.Company = e1.Company AND e2.Salary < e1.Salary) As 'rank'
FROM employees e1 ORDER BY e1.Company,e1.Salary) AS t1
JOIN (select Company,COUNT(*) as cnt
     from employees 
     group by Company) AS t2
ON t1.Company = t2.Company
where t1.rank in (ceiling(t2.cnt/2.0),t2.cnt/2+1);
-- 6/2=3,6/2+1=4 偶数
-- 5/2=2.5,5/2+1=3.5,奇数都不满足 
-- ceiling(5/2) = 3 奇数

但是返回是这样

因为我们rank当有一样的值的时候是一样的排序,可是中位数不是这样取的啊

所以row_number可以实现这样的


with cte as(

select Id,Company,salary 

,ROW_NUMBER() over(partition by Company order by salary) as rnk

,count(*) over(partition by Company) as cnt

from employees

)select Id,Company,salary from cte

where rnk in (CEILING(cnt/2.0),cnt/2+1)

但是emmm,mysql不支持row_number

这样实现row_number

set @row=0;
set @mid='';
SELECT * FROM
(SELECT *,
       case when @mid = Company then @row:=@row+1 else @row:=1 end 'rank',
       @mid:=Company
FROM employees
order by Company,salary) AS t1
JOIN (select Company,COUNT(*) as cnt
     from employees 
     group by Company) AS t2
ON t1.Company = t2.Company
where t1.rank in (ceiling(t2.cnt/2.0),t2.cnt/2+1);

参考链接:https://blog.csdn.net/yige9394/article/details/80506689

所以真正的可改成上面那样

 

 

 

 

    原文作者:上进的菜鸟
    原文地址: https://blog.csdn.net/kylin_learn/article/details/90473470
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。