SQL--查找每个公司薪水的中位数_sql查询每个部门薪水的中位数

题目
F0126表包含所有员工，其中有三列：员工ID，公司名和薪水。

请编写SQL查询来查找每个公司的薪水中位数。

中位数：

若记录数为奇数，取一条，否则取两条，如记录数为7，按顺序直接取第4名即是中位数，记录数为6，按顺序则是第3，4名是中位数

结果

测试数据

CREATE TABLE F0126
(
ID INT,
Company VARCHAR(10),
Salary INT
)

INSERT INTO F0126 VALUES
(1,'A',8341),
(2,'A',9410),
(3,'A',10050),
(4,'A',15314),
(5,'A',8451),
(6,'A',9513),
(7,'B',10005),
(8,'B',13000),
(9,'B',11540),
(10,'B',10345),
(11,'B',12210),
(12,'B',9234),
(13,'C',12000),
(14,'C',8900),
(15,'C',9000),
(16,'C',10100),
(17,'C',8000)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

解法一

• 自定义变量

SELECT a.ID,a.Company,a.Salary FROM(
	SELECT ID,Company,Salary, @ranking:=IF(@pre=Company, @ranking+1,1) AS ranking,@pre:=Company
	FROM f0126, (SELECT @ranking:=0,@pre:=NULL) c
	ORDER BY Company,Salary,ID) a
JOIN (
	SELECT ID,Company,Salary,@ranking:=IF(@pre=Company,@ranking+1,1) AS ranking,@pre:=Company
	FROM f0126, (SELECT @ranking:=0, @pre:=NULL) c
	ORDER BY Company,Salary DESC,ID ) b
ON a.ID = b.ID
WHERE ABS(a.ranking-b.ranking) <= 1
1
2
3
4
5
6
7
8
9
10

解法二

• 开窗函数

SELECT a.ID, a.Company,a.Salary FROM (
	SELECT *,ROW_NUMBER()OVER(PARTITION BY Company ORDER BY Salary) AS rnk FROM f0126) a 
	JOIN 
	(SELECT *,ROW_NUMBER()OVER(PARTITION BY Company ORDER BY Salary DESC) AS rnk FROM f0126) b
	ON a.ID = b.ID 
WHERE abs(cast(a.rnk as signed)-cast(b.rnk as signed)) <= 1
1
2
3
4
5
6

解法三

• 利用判断

SELECT ID,Company,Salary FROM(
	SELECT a.*, b.MAX_ranking FROM(
		SELECT *,ROW_NUMBER()OVER(PARTITION BY Company ORDER BY Salary) AS ranking FROM f0126) a
	JOIN 
	(SELECT Company,MAX(ranking) AS MAX_ranking FROM (
		SELECT *,ROW_NUMBER()OVER(PARTITION BY Company ORDER BY Salary) AS ranking FROM f0126
		) c
		GROUP BY Company) b
	ON a.Company = b.Company) d
WHERE ranking = IF (MAX_ranking % 2 = 0, MAX_ranking/2, (MAX_ranking+1)/2) OR
	ranking = IF (MAX_ranking % 2 = 0, MAX_ranking/2 + 1, (MAX_ranking+1)/2)
GROUP BY Company,ranking
1
2
3
4
5
6
7
8
9
10
11
12