Oracle HR Schema：查询各部门薪资与后续同事均值差最大的员工

以下是符合要求的Oracle SQL查询语句，使用分析函数及窗口子句实现需求：

WITH emp_sal_diff AS (
    SELECT
        employee_id,
        first_name || ' ' || last_name AS full_name,
        department_id,
        salary,
        hire_date,
        -- 处理无后续员工的情况，将平均薪资设为0
        NVL(AVG(salary) OVER (
            PARTITION BY department_id
            ORDER BY hire_date
            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
        ), 0) AS avg_following_sal,
        -- 计算薪资差值：自身薪资 - 后续入职同事平均薪资
        salary - NVL(AVG(salary) OVER (
            PARTITION BY department_id
            ORDER BY hire_date
            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
        ), 0) AS sal_diff
    FROM employees
    WHERE department_id IS NOT NULL -- 排除无部门的员工
)
SELECT
    employee_id,
    full_name,
    department_id,
    salary,
    hire_date,
    avg_following_sal,
    sal_diff AS max_salary_difference
FROM (
    SELECT
        *,
        -- 按部门分组，对差值降序排名，取排名第一的记录
        RANK() OVER (
            PARTITION BY department_id
            ORDER BY sal_diff DESC
        ) AS diff_rank
    FROM emp_sal_diff
)
WHERE diff_rank = 1;

核心逻辑说明 #

• 窗口子句计算后续平均薪资：通过AVG(salary) OVER (...)搭配ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING，精准定位当前员工之后入职的所有同部门同事，计算他们的平均薪资；用NVL()处理无后续员工的场景，此时后续平均薪资视为0，匹配你给出的部门60示例结果。
• 差值排名筛选：用RANK()分析函数按部门分组，对薪资差值降序排名，最终筛选出每个部门排名第一的记录，即为差值最大的员工。

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