Create table If Not Exists Candidates (employee_id int, experience ENUM('Senior', 'Junior'), salary int)
Truncate table Candidates
insert into Candidates (employee_id, experience, salary) values ('1', 'Junior', '10000')
insert into Candidates (employee_id, experience, salary) values ('9', 'Junior', '15000')
insert into Candidates (employee_id, experience, salary) values ('2', 'Senior', '20000')
insert into Candidates (employee_id, experience, salary) values ('11', 'Senior', '16000')
insert into Candidates (employee_id, experience, salary) values ('13', 'Senior', '50000')
insert into Candidates (employee_id, experience, salary) values ('4', 'Junior', '40000')
一家公司想雇佣新员工。公司的工资预算是 7 万美元。公司的招聘标准是:
继续雇佣薪水最低的高级职员,直到你不能再雇佣更多的高级职员。
用剩下的预算雇佣薪水最低的初级职员。
继续以最低的工资雇佣初级职员,直到你不能再雇佣更多的初级职员。
编写一个SQL查询,查找根据上述条件雇用职员的 ID。
按 任意顺序 返回结果表。
查询结果格式如下例所示。
解题思路
第一个sum() over() 用来确定Senior的employee_id
第二个sum() over() 用来确定Junior的employee_id
代码
# Write your MySQL query statement below
select employee_id
from
(select *,70000-sum(salary) over(order by experience_rk,salary) as sum_salary2
from
(select *,70000-sum(salary) over(partition by experience order by salary) as sum_salary,if(experience='Senior',1,2) as experience_rk
from candidates) t
where t.sum_salary>=0) t2
where t2.sum_salary2>=0
。