leetcode569. 従業員の給与中央値

Employee表には、すべての従業員が含まれています.Employee表には、従業員Id、会社名、給料の3つの列があります.

+-----+------------+--------+
|Id   | Company    | Salary |
+-----+------------+--------+
|1    | A          | 2341   |
|2    | A          | 341    |
|3    | A          | 15     |
|4    | A          | 15314  |
|5    | A          | 451    |
|6    | A          | 513    |
|7    | B          | 15     |
|8    | B          | 13     |
|9    | B          | 1154   |
|10   | B          | 1345   |
|11   | B          | 1221   |
|12   | B          | 234    |
|13   | C          | 2345   |
|14   | C          | 2645   |
|15   | C          | 2645   |
|16   | C          | 2652   |
|17   | C          | 65     |
+-----+------------+--------+

SQLクエリーを作成して、各会社の給与の中位数を検索してください.チャレンジポイント:内蔵SQL関数を使用せずにこの問題を解決できるかどうか.

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

 

select
    company_rownumber.Id as Id,
    company_rownumber.Company as Company,
    company_rownumber.Salary as Salary
from
#     row_number，         
(select
    Id,
    Company,
    Salary,
    @company_no:=case when @company_name = company then @company_no+1 else 1 end as company_no,
    @company_name:=company
from
    (select id,company,salary from employee,(select @company_no:=0,@company_name:="") b) c
order by 
    Company,
    Salary) company_rownumber
join
#               
(select
    info.Id,
    info.Company,
    info.Salary,
    cnt
from
(select
    Id,
    Company,
    Salary
from
    Employee) info
join
(select
    Company,
    count(1) as cnt
from
    Employee
group by
    Company) company_cnt
on
    info.Company = company_cnt.Company) company_group_cnt
on
    company_rownumber.Id = company_group_cnt.Id
#       row_number          
where
    company_no >= cnt/2 
    and company_no <= cnt/2+1