使用GROUPING SETS的SQL返回原表？原因排查与修正方法

我有如下示例表：

我编写了一条SQL查询，用于获取按不同列分组后的平均薪资：

SELECT
    name,
    manager,
    country,
    position,
    AVG(salary)
FROM
    table
GROUP BY GROUPING SETS
    (manager),
    (name, country),
    (position),
    ()

但输出结果基本与原表一致，仅顺序不同。请问这是什么原因？该如何修正此查询以得到我需要的分组结果？

1. SELECT子句与分组键不匹配：你在SELECT中包含了所有列，但GROUPING SETS仅指定了(manager)、(name,country)、(position)、()这几种分组组合。对于不在当前分组键中的列（比如按manager分组时的name/country/position），若数据库未开启严格的ONLY_FULL_GROUP_BY模式，会返回分组内的任意值，导致结果看起来和原表数据几乎一致；若开启严格模式，这条SQL会直接报错。
2. 表名使用关键字：table是SQL保留关键字，直接使用会引发语法问题（部分数据库可能兼容，但不符合规范）。

要得到正确的分组结果，SELECT子句只能包含分组键和聚合函数，非分组键的列要么用聚合函数处理，要么通过GROUPING()函数标记状态，或者用CASE语句区分显示。

方案1：添加分组标记，清晰区分不同分组结果 #

SELECT
    name,
    manager,
    country,
    position,
    AVG(salary) AS avg_salary,
    -- 标记列是否属于当前分组：1表示不在分组中，0表示在分组中
    GROUPING(name) AS name_not_in_group,
    GROUPING(manager) AS manager_not_in_group,
    GROUPING(country) AS country_not_in_group,
    GROUPING(position) AS position_not_in_group
FROM
    `table` -- 用反引号包裹关键字表名
GROUP BY GROUPING SETS
    (manager),
    (name, country),
    (position),
    ()

方案2：仅显示当前分组的有效列，其余置为NULL #

SELECT
    CASE WHEN GROUPING(name) = 0 THEN name END AS name,
    CASE WHEN GROUPING(manager) = 0 THEN manager END AS manager,
    CASE WHEN GROUPING(country) = 0 THEN country END AS country,
    CASE WHEN GROUPING(position) = 0 THEN position END AS position,
    AVG(salary) AS avg_salary
FROM
    `table`
GROUP BY GROUPING SETS
    (manager),
    (name, country),
    (position),
    ()

修正后结果说明 #

• 按manager分组：name/country/position为NULL，显示每个经理下属的平均薪资
• 按name, country分组：manager/position为NULL，显示对应人员的薪资（因每人仅一条数据，结果等于自身薪资）
• 按position分组：name/manager/country为NULL，显示各职位的平均薪资
• 空分组()：所有列为NULL，显示全表的平均薪资

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