MySQL技术问题：查询公司内薪资涨幅最大的员工

要解决这个问题，我们需要结合employees和salaries两张表，先计算每位员工的薪资涨幅，再定位出涨幅最大的那位员工。

先明确表结构 #

首先梳理你提供的两张表定义（补充了salaries表常见的复合主键，因为单员工会有多条薪资记录）：

CREATE TABLE employees (
    emp_no INT NOT NULL,
    birth_date DATE NOT NULL,
    first_name VARCHAR(14) NOT NULL,
    last_name VARCHAR(16) NOT NULL,
    gender ENUM ('M','F') NOT NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (emp_no)
);

CREATE TABLE salaries (
    emp_no INT NOT NULL,
    salary INT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    PRIMARY KEY (emp_no, from_date)
);

解法思路 #

薪资涨幅通常有两种主流计算逻辑，我分别给出对应的SQL方案：

1. 首次薪资与最后薪资的差值：贴合“在职期间薪资变化”的直观理解，即员工入职后第一笔薪资和当前（或在职最后阶段）薪资的差额
2. 最高薪资与最低薪资的差值：体现员工在职期间达到的最大薪资浮动空间

方案1：计算首次 vs 最后薪资的涨幅

WITH emp_salary_changes AS (
    SELECT
        s.emp_no,
        -- 提取员工首次入职时的薪资
        FIRST_VALUE(s.salary) OVER (PARTITION BY s.emp_no ORDER BY s.from_date) AS initial_salary,
        -- 提取员工最新的薪资（在职员工的to_date一般为'9999-01-01'，取最新生效的薪资）
        LAST_VALUE(s.salary) OVER (PARTITION BY s.emp_no ORDER BY s.from_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final_salary,
        -- 计算涨幅金额
        LAST_VALUE(s.salary) OVER (PARTITION BY s.emp_no ORDER BY s.from_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - FIRST_VALUE(s.salary) OVER (PARTITION BY s.emp_no ORDER BY s.from_date) AS salary_increase
    FROM salaries s
    -- 如果只需要筛选在职员工，取消下面两行注释
    -- JOIN employees e ON s.emp_no = e.emp_no
    -- WHERE s.to_date = '9999-01-01'
)
SELECT
    e.emp_no,
    e.first_name,
    e.last_name,
    esc.initial_salary,
    esc.final_salary,
    esc.salary_increase
FROM emp_salary_changes esc
JOIN employees e ON esc.emp_no = e.emp_no
WHERE esc.salary_increase = (SELECT MAX(salary_increase) FROM emp_salary_changes)
GROUP BY e.emp_no, e.first_name, e.last_name, esc.initial_salary, esc.final_salary, esc.salary_increase;

方案2：计算最高 vs 最低薪资的涨幅

WITH emp_salary_range AS (
    SELECT
        emp_no,
        MIN(salary) AS min_salary,
        MAX(salary) AS max_salary,
        MAX(salary) - MIN(salary) AS salary_increase
    FROM salaries
    -- 如果只需要筛选在职员工，取消下面一行注释
    -- WHERE to_date = '9999-01-01'
    GROUP BY emp_no
)
SELECT
    e.emp_no,
    e.first_name,
    e.last_name,
    esr.min_salary,
    esr.max_salary,
    esr.salary_increase
FROM emp_salary_range esr
JOIN employees e ON esr.emp_no = e.emp_no
WHERE esr.salary_increase = (SELECT MAX(salary_increase) FROM emp_salary_range);

额外说明 #

• 如果你的数据库版本不支持CTE（比如MySQL 5.7及更早版本），可以把CTE部分改写为子查询形式，逻辑是完全一致的
• 若要精准筛选在职员工，记得打开代码中对应的WHERE条件注释，通常在职员工的to_date会设为9999-01-01这类特殊值

内容的提问来源于stack exchange，提问作者user9609362