MySQL高阶2010-职员招聘人数2

目录

题目

准备数据

分析数据

总结

---

题目

一家公司想雇佣新员工。公司的工资预算是 $70000 。公司的招聘标准是：

1. 继续雇佣薪水最低的高级职员，直到你不能再雇佣更多的高级职员。
2. 用剩下的预算雇佣薪水最低的初级职员。
3. 继续以最低的工资雇佣初级职员，直到你不能再雇佣更多的初级职员。

编写一个解决方案，查找根据上述条件雇用职员的 ID。
按 任意顺序 返回结果表。

准备数据

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')

分析数据

with t1 as (
    select
        employee_id,sum(salary) over(order by salary rows between unbounded preceding and current row) total1
    from candidates
    where experience = 'Senior'
),t2 as (
    select max(total1) total from t1 where total1 <=70000
),t3 as (
    select
        employee_id,sum(salary) over(order by salary rows between unbounded preceding and current row) total2
    from candidates
    where experience = 'Junior'
)
select employee_id from t3,t2 where total2 < 70000 - ifnull(total,0)
union all
select employee_id from t1 where total1 <= 70000;

总结

与2004题不同的是,2004求个数,而2010是求employee_id.