# $569. 员工薪水中位数
# https://leetcode-cn.com/problems/median-employee-salary/

# SQL架构
Create table If Not Exists Employee (id int, company varchar(255), salary int);
Truncate table Employee;
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');

# Write your MySQL query statement below
select
    # sql_mode=only_full_group_by
    any_value(e1.id),
    e1.company,
    e1.salary
from
    Employee e1
    left join Employee e2 on e1.company = e2.company
group by
    # sql_mode=only_full_group_by
    e1.id,
    e1.company,
    e1.salary
having
    sum(
        case
            when e1.salary = e2.salary then 1
            else 0
        end
    ) >= abs(sum(sign(e1.salary - e2.salary)))
order by
    e1.id;

# clean-up
drop table Employee;