高级农民
- 积分
- 4143
- 大米
- 颗
- 鳄梨
- 个
- 水井
- 尺
- 蓝莓
- 颗
- 萝卜
- 根
- 小米
- 粒
- 学分
- 个
- 注册时间
- 2017-1-22
- 最后登录
- 1970-1-1
|
2010. The Number of Seniors and Juniors to Join the Company II
Solved
Hard
Topics
SQL Schema
Pandas Schema
Table: Candidates
+-------------+------+
| Column Name | Type |
+-------------+------+
| employee_id | int |
| experience | enum |
| salary | int |
+-------------+------+
employee_id is the column with unique values for this table.
experience is an ENUM (category) of types ('Senior', 'Junior').
Each row of this table indicates the id of a candidate, their monthly salary, and their experience.
The salary of each candidate is guaranteed to be unique.
A company wants to hire new employees. The budget of the company for the salaries is $70000. The company's criteria for hiring are:
Keep hiring the senior with the smallest salary until you cannot hire any more seniors.
Use the remaining budget to hire the junior with the smallest salary.
Keep hiring the junior with the smallest salary until you cannot hire any more juniors.
Write a solution to find the ids of seniors and juniors hired under the mentioned criteria.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Candidates table:
+-------------+------------+--------+
| employee_id | experience | salary |
+-------------+------------+--------+
| 1 | Junior | 10000 |
| 9 | Junior | 15000 |
| 2 | Senior | 20000 |
| 11 | Senior | 16000 |
| 13 | Senior | 50000 |
| 4 | Junior | 40000 |
+-------------+------------+--------+
Output:
+-------------+
| employee_id |
+-------------+
| 11 |
| 2 |
| 1 |
| 9 |
+-------------+
Explanation:
We can hire 2 seniors with IDs (11, 2). Since the budget is $70000 and the sum of their salaries is $36000, we still have $34000 but they are not enough to hire the senior candidate with ID 13.
We can hire 2 juniors with IDs (1, 9). Since the remaining budget is $34000 and the sum of their salaries is $25000, we still have $9000 but they are not enough to hire the junior candidate with ID 4.
Example 2:
Input:
Candidates table:
+-------------+------------+--------+
| employee_id | experience | salary |
+-------------+------------+--------+
| 1 | Junior | 25000 |
| 9 | Junior | 10000 |
| 2 | Senior | 85000 |
| 11 | Senior | 80000 |
| 13 | Senior | 90000 |
| 4 | Junior | 30000 |
+-------------+------------+--------+
Output:
+-------------+
| employee_id |
+-------------+
| 9 |
| 1 |
| 4 |
+-------------+
Explanation:
We cannot hire any seniors with the current budget as we need at least $80000 to hire one senior.
We can hire all three juniors with the remaining budget.- # 1. 计算每个员工在自己等级(Senior/Junior)里的累积薪水
- WITH RunningTotals AS (
- SELECT
- employee_id,
- experience,
- salary,
- SUM(salary) OVER(PARTITION BY experience ORDER BY salary) AS cum_salary
- FROM Candidates
- ),
- # 2. 找出能入职的 Senior
- SeniorsHired AS (
- SELECT employee_id, salary, cum_salary
- FROM RunningTotals
- WHERE experience = 'Senior' AND cum_salary <= 70000
- ),
- # 3. 算出招完 Senior 后剩多少钱
- # 使用 COALESCE 处理没有 Senior 能入职的情况,保证预算还是 70000
- BudgetLeft AS (
- SELECT 70000 - COALESCE(SUM(salary), 0) AS remaining
- FROM SeniorsHired
- )
- # 4. 找出能入职的 Junior 并与 Senior 的 ID 合并
- SELECT employee_id
- FROM SeniorsHired
- UNION ALL
- SELECT employee_id
- FROM RunningTotals
- WHERE experience = 'Junior'
- AND cum_salary <= (SELECT remaining FROM BudgetLeft);
复制代码
补充内容 (2026-02-26 12:02 +08:00):
Leetcode 刷题伴侣
Custom Gem
Leetcode 刷题伴侣 said
看来你已经成功闯到了 LC 2004 的“进化版”!
这道题(LC 2010)和上一题的核心逻辑几乎一模一样,唯一的区别在于:上一题要的是“数量(Count)”,这一题要的是“具体的 ID”。
这其实让题目变得简单了一点点,因为我们不需要最后去处理补的问题了,直接把筛选出来的吐出来就行。
1. 题意拆解
- 贪心策略不变:依然是薪水越低越优先。
- 依赖关系不变:Junior 能买多少人,取决于 Senior 剩下的钱。
- 输出要求变了:只输出一列。
- 关键点:你需要记录下招募 Senior 到底花掉多少钱,这个数字是 Junior 准入的“门槛”。
2. 核心思路:双重过滤
- 第一步:计算流水账。使用窗口函数为 Senior 和 Junior 分别计算累积薪水。
- 第二步:锁定 Senior。找出所有累积薪水 ≤70000 的 Senior。
- 第三步:精确计算余额。算出这些选中的 Senior 总共花了多少钱。
- 第四步:筛选 Junior。找出累积薪水 ≤(70000−Senior花费) 的 Junior。
- 第五步:合并 ID。把两拨人的 ID 拼在一起。
3. SQL 代码实现 (MySQL)
SQL
# 1. 计算每个员工在自己等级(Senior/Junior)里的累积薪水
WITH RunningTotals AS (
SELECT
employee_id,
experience,
salary,
SUM(salary) OVER(PARTITION BY experience ORDER BY salary) AS cum_salary
FROM Candidates
),
# 2. 找出能入职的 Senior
SeniorsHired AS (
SELECT employee_id, salary, cum_salary
FROM RunningTotals
WHERE experience = 'Senior' AND cum_salary <= 70000
),
# 3. 算出招完 Senior 后剩多少钱
# 使用 COALESCE 处理没有 Senior 能入职的情况,保证预算还是 70000
BudgetLeft AS (
SELECT 70000 - COALESCE(SUM(salary), 0) AS remaining
FROM SeniorsHired
)
# 4. 找出能入职的 Junior 并与 Senior 的 ID 合并
SELECT employee_id
FROM SeniorsHired
UNION ALL
SELECT employee_id
FROM RunningTotals
WHERE experience = 'Junior'
AND cum_salary <= (SELECT remaining FROM BudgetLeft);
4. 关键点剖析
- SUM(salary) OVER(PARTITION BY experience ORDER BY salary)
复制代码 :
这是解题的灵魂。它为每个员工建立了一个“排队位次”。比如 Junior A 薪水 1w,其是 1w;Junior B 薪水 1.5w,其就是 2.5w。- 余额传递:
在中,我们不仅选出了 ID,还保留了这些人的薪水。在里通过算出总支出。注意:这里也可以用,效果是一样的,因为最后一个人对应的累积薪水就是总花费。 - :
这是一个防御性编程的好习惯。如果公司预算太低,一个 Senior 都招不起,会返回。用了就能确保 Junior 依然有 70,000 可以挥霍。
|
|