SQL技术问询：如何查询各部门的最高与最低工资及对应员工姓名

针对你需要获取各部门最高、最低工资及对应员工姓名的需求，这里提供两种实用的SQL解决方案，适配不同场景：

方案1：使用窗口函数（精准匹配单个员工） #

如果部门内同薪资的员工仅需展示其中一位，可使用ROW_NUMBER()窗口函数按部门分组、薪资排序后筛选排名第一的记录：

WITH max_sal_records AS (
    SELECT 
        dept,
        salary AS max_salary,
        name AS max_salary_employee,
        ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_num
    FROM employees
),
min_sal_records AS (
    SELECT 
        dept,
        salary AS min_salary,
        name AS min_salary_employee,
        ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary ASC) AS rank_num
    FROM employees
)
SELECT 
    m.dept,
    m.max_salary,
    m.max_salary_employee,
    n.min_salary,
    n.min_salary_employee
FROM max_sal_records m
INNER JOIN min_sal_records n 
    ON m.dept = n.dept 
    AND m.rank_num = 1 
    AND n.rank_num = 1;

方案2：处理同部门同薪资的多个员工 #

如果部门内存在多名员工薪资同为最高/最低，可通过聚合函数拼接所有对应员工姓名：

WITH dept_max_sal AS (
    SELECT 
        dept,
        MAX(salary) AS max_salary,
        GROUP_CONCAT(DISTINCT name SEPARATOR ', ') AS max_salary_employees
    FROM employees
    GROUP BY dept
),
dept_min_sal AS (
    SELECT 
        dept,
        MIN(salary) AS min_salary,
        GROUP_CONCAT(DISTINCT name SEPARATOR ', ') AS min_salary_employees
    FROM employees
    GROUP BY dept
)
SELECT 
    dm.dept,
    dm.max_salary,
    dm.max_salary_employees,
    dn.min_salary,
    dn.min_salary_employees
FROM dept_max_sal dm
INNER JOIN dept_min_sal dn 
    ON dm.dept = dn.dept;

补充说明 #

• 方案1中ROW_NUMBER()会给同薪资员工分配不同排名，若需保留所有同薪资员工，可替换为RANK()或DENSE_RANK()，再调整筛选逻辑。
• 方案2使用GROUP_CONCAT（MySQL语法），其他数据库可替换为对应函数：比如PostgreSQL用STRING_AGG，SQL Server用STRING_AGG或STUFF+FOR XML PATH组合。

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