建表
drop table if EXISTS employee;
create table employee
(
Id int primary key auto_increment,
Company char,
Salary DECIMAL
);
insert into employee values(1, 'A', 2341);
insert into employee values(null, 'A', 341);
insert into employee values(null, 'A', 15);
insert into employee values(null, 'A', 15314);
insert into employee values(null, 'A', 451);
insert into employee values(null, 'A', 513);
insert into employee values(null, 'B', 15);
insert into employee values(null, 'B', 13);
insert into employee values(null, 'B', 1154);
insert into employee values(null, 'B', 1345);
insert into employee values(null, 'B', 1221);
insert into employee values(null, 'B', 234);
insert into employee values(null, 'C', 2345);
insert into employee values(null, 'C', 2645);
insert into employee values(null, 'C', 2645);
insert into employee values(null, 'C', 2652);
insert into employee values(null, 'C', 65);
首先明確：
中位數，位置在最中間的數
- 中位數的位置：
當樣本數為奇數時：中位數為第(N+1)/2個資料
當樣本數為偶數時：中位數為第N/2個資料與第N/2+1個資料的算術平均值
偶數時，根據題意，N/2和N/2+1的數都取到了
- 算出每個公司的人數、薪水的排序
- where 選擇，根據人數奇偶篩選
select Id, Company, Salary
from
(
select *, row_number() over (partition by Company order by Salary) rnk,
count(*) over (partition by Company) num
from Employee
) t
where(
-- (num%2=1 and rnk = floor(num/2)+1)
(num%2=1 and rnk = (num+1)/2)
or
-- (num%2=0 and (rnk = floor(num/2) or rnk = floor(num/2)+1))
(num%2=0 and (rnk = num/2 or rnk = (num/2)+1))
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/162634.html
標籤：其他
上一篇：黃毅然的資料庫學習（二）
下一篇：學生資訊管理系統