SQL按name分组查询时department列输出规则及自定义实现问题

1. 你编写的SQL中department列的输出规则 #

• 首先在严格遵循SQL标准的数据库（包括开启ONLY_FULL_GROUP_BY模式的MySQL、PostgreSQL、Oracle、SQL Server等）中，这条SQL会直接执行报错：SQL要求SELECT子句中所有非聚合函数的列，必须全部出现在GROUP BY子句中，你的查询里department没有出现在GROUP BY里，也没有用聚合函数包裹，不符合语法规范。
• 只有在关闭严格校验的低版本MySQL等少数数据库中，这条SQL才能执行，此时department的输出值没有固定规则：数据库会随机读取分组内任意一行的department值返回，结果完全取决于数据存储顺序、查询扫描逻辑，不可控也不可复现，没有业务参考价值。

2. 控制分组后非分组列输出的相关函数 #

现在主流的现代数据库都提供了对应函数来合法控制该类场景的输出结果：

• 通用兼容函数ANY_VALUE(department)：显式告知数据库随机返回分组内任意一行的department值，和关闭严格模式的执行效果一致，且语法符合规范，不会在严格模式下报错。
• 定向取值窗口函数：FIRST_VALUE()、LAST_VALUE()，可按指定排序规则取分组内第一行/最后一行的对应列值。
• 快捷取值函数：MAX_BY(返回列, 排序列)、MIN_BY(返回列, 排序列)，可直接返回分组内排序列最大/最小值对应的返回列值，部分新数据库版本支持。

3. 输出每个name对应最高salary所在department的实现方案 #

完全可以实现，这里提供两种常用方案：

方案1：窗口函数方案（兼容所有支持SQL标准的现代数据库）

WITH salary_ranks AS (
    SELECT
        name,
        salary,
        department,
        -- 按name分组，薪资从高到低排序，最高薪行的排序值为1
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY salary DESC) AS rank_num
    FROM 你的表名
)
-- 取每个name下排序第一的行即可
SELECT name, salary, department FROM salary_ranks WHERE rank_num = 1;

如果同一个name下有多个相同的最高薪资，需要全部返回时，把ROW_NUMBER()替换为RANK()即可。

方案2：MAX_BY快捷方案（支持MySQL8.0.22+、PostgreSQL16+、Spark SQL等）

如果你同时需要统计平均薪资和最高薪资对应的部门，这个写法更简洁：

SELECT
    name,
    AVG(salary) AS average_salary,
    MAX_BY(department, salary) AS highest_salary_department
FROM 你的表名
GROUP BY name;

按照你提供的示例数据，最终输出结果为：

内容的提问来源于stack exchange，提问作者S.Slusky