【MySQL】临时变量使用之职员招聘人数（额外小知识点：SQL查询为空设置默认值）

力扣题

1、题目地址

2004. 职员招聘人数

2、模拟表

表：Candidates

Column Name	Type
employee_id	int
experience	enum
salary	int

• employee_id是此表的主键列。
• 经验是包含一个值（“高级”、“初级”）的枚举类型。
• 此表的每一行都显示候选人的id、月薪和经验。

3、要求

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

1. 雇佣最多的高级员工。
2. 在雇佣最多的高级员工后，使用剩余预算雇佣最多的初级员工。

编写一个SQL查询，查找根据上述标准雇佣的高级员工和初级员工的数量。

按 任意顺序 返回结果表。

4、示例

示例一

输入：

Candidates 表：

employee_id	experience	salary
1	Junior	10000
9	Junior	10000
2	Senior	20000
11	Senior	20000
13	Senior	50000
4	Junior	40000

输出：

experience	accepted_candidates
Senior	2
Junior	2

说明：

我们可以雇佣2名ID为（2,11）的高级员工。
由于预算是7万美元，他们的工资总额是4万美元，我们还有3万美元，但他们不足以雇佣ID为13的高级员工。

我们可以雇佣2名ID为（1,9）的初级员工。
由于剩下的预算是3万美元，他们的工资总额是2万美元，我们还有1万美元，但他们不足以雇佣ID为4的初级员工。

示例二

输入：
Candidates 表：

employee_id	experience	salary
1	Junior	10000
9	Junior	10000
2	Senior	80000
11	Senior	80000
13	Senior	80000
4	Junior	40000

输出：

experience	accepted_candidates
Senior	0
Junior	3

解释：

我们不能用目前的预算雇佣任何高级员工，因为我们需要至少80000美元来雇佣一名高级员工。
我们可以用剩下的预算雇佣三名初级员工。

5、代码编写

我的代码

# 查询高级员工且按工资正序，加序号是为了进行累加计算
with tmp as (
    select *, row_number() over (order by salary) AS rn
    from Candidates
    where experience = 'Senior'
), 
# 根据序号进行工资累加计算，取到小于等于70000即可，然后算出剩余的钱
tmp2 as (
    select 70000-sum(b.salary) AS money
    from tmp a, tmp b 
    where a.rn >= b.rn
    group by a.rn
    having sum(b.salary) <= 70000
    order by a.rn desc
    limit 1
), 
# 因为可能存在雇不起高级员工的情况，查询为空，就需要设置默认值70000（工资预算）
tmp3 as (
    select money from tmp2
    union all
    select 70000
    where not exists(select money from tmp2)
), 
#  查询初级员工且按工资正序，加序号是为了进行累加计算
tmp4 as (
    select *, row_number() over (order by salary) AS rn
    from Candidates
    where experience = 'Junior'
), 
# 根据序号进行工资累加计算，取到小于等于70000即可，利用序号倒序取出所雇人数
tmp5 as (
    select 'Senior' AS experience, count(*) AS accepted_candidates
    from tmp a, tmp b 
    where a.rn >= b.rn
    group by a.rn
    having sum(b.salary) <= 70000
    order by a.rn desc
    limit 1
), 
# 因为可能出现查询为空的情况，所以需要设置默认值'Senior', 0（高级员工默认招人数）
tmp6 as (
    select * from tmp5
    union all
    select 'Senior', 0
    where not exists(select * from tmp5)
), 
# 根据序号进行工资累加计算，取到小于等于雇高级员工后剩余的钱即可，利用序号倒序取出所雇人数
tmp7 as (
    select 'Junior' AS experience, count(*) AS accepted_candidates
    from tmp4 a, tmp4 b
    where a.rn >= b.rn
    group by a.rn
    having sum(b.salary) <= (select money from tmp3)
    order by a.rn desc
    limit 1
),
# 因为可能出现查询为空的情况，所以需要设置默认值'Junior', 0（初级员工默认招人数）
tmp8 as (
    select * from tmp7
    union all
    select 'Junior', 0
    where not exists(select * from tmp7)
)
select * from tmp6
union all
select * from tmp8

网友代码（临时变量写法）

with temp as(
    select experience,
           salary,
           (case when @tmp>=salary then 1 else 0 end) x,
           @tmp:=if(@tmp>=salary, @tmp-salary, @tmp) y
    from (
        select *, 
               @tmp:=70000
        from Candidates
    ) z
    order by experience, salary
)

select experience,
       sum(x) accepted_candidates 
from temp
group by experience

里面有个点很奇怪，英文顺序 J K L M N O P Q R S，J明明在S前面，用倒序S竟然会出现在J后面（这个暂时不理解）

select *
from Candidates
order by experience desc, salary

期望结果

| employee_id | experience | salary |
| ----------- | ---------- | ------ |
| 2           | Senior     | 20000  |
| 11          | Senior     | 20000  |
| 13          | Senior     | 50000  |
| 1           | Junior     | 10000  |
| 9           | Junior     | 10000  |
| 4           | Junior     | 40000  |

实际结果

| employee_id | experience | salary |
| ----------- | ---------- | ------ |
| 1           | Junior     | 10000  |
| 9           | Junior     | 10000  |
| 4           | Junior     | 40000  |
| 2           | Senior     | 20000  |
| 11          | Senior     | 20000  |
| 13          | Senior     | 50000  |

知识点

SQL查询为空设置默认值

在前面我的写法里面，两个地方用到了设置默认值

select money from tmp2
union all
select 70000
where not exists(select money from tmp2)

select * from tmp5
union all
select 'Senior', 0
where not exists(select * from tmp5)

会发现两个关键点
1、union all 前面的部分会在 union all 后面 not exists 里面出现
2、union all 后面的语句是没有连表的（这个是绝对不能连表的，测试过了）
其中 select 70000 和 select 'Senior', 0 就是设置默认值

临时变量

用法可参考我之前写的文章：【MySQL】临时变量用法