MySQL 高级版 2004 - 招聘人员

最编程 2024-10-01 15:41:20

...

第一步:筛选"Senior"的累计salary总和

因为根据salary排序,所以开窗函数sum中salary相同的值相同,所以employee_id = 2 或 11的薪水都是40000

select employee_id,sum(salary) over(order by salary) total1 from candidates
where experience = 'Senior';

第二步:t2子查询选择最大值小于70000

with t1 as (
    select employee_id,sum(salary) over(order by salary) total1 from candidates
    where experience = 'Senior'
),t2 as (
    select max(total1) total from t1 where total1 <= 70000
)select * from t2;

第三步:筛选"Junior"的累计salary总和

with t1 as (
    select employee_id,sum(salary) over(order by salary) 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) total2 from candidates
    where experience = 'Junior'
)select * from t3;

第四步:第一个子查询从t1中选择"Senior"中小于或等于70000,第二个select子查询再从t2和t3中选择"Junior"出小于70000减去第一个select子查询.最后通过union all将两个结果结合起来.

with t1 as (
    select employee_id,sum(salary) over(order by salary) 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) total2 from candidates
    where experience = 'Junior'
)
select 'Senior' as experience,count(distinct  employee_id) accepted_candidates from t1
where total1 <= 70000
union all
select 'Junior' as experience,count(distinct  employee_id) accepted_candidates from t2,t3
where total2 < 70000 - ifnull(total,0);

上一篇：记录｜Modbus-TCP 产品使用记录【Modus Drive】-A、Modus Drive

下一篇：华为-IPv6 与 IPv4 网络互通 6to4 自动隧道配置实验