使用子查询/CTE求部门薪资总和最值的SQL查询失败问题

你原来的查询逻辑存在核心错误：

• 子查询sum_sal_per_dpt已经按dpt_id分组，算出了每个部门的薪资总和total_salaries，每个部门对应唯一的一条记录。
• 外层查询再按dpt_id分组时，每个分组内只有一条数据，此时max(total_salaries)和min(total_salaries)作用在单个值上，结果就是这个值本身。
• 两个select语句本质上都等价于select dpt_id, total_salaries from sum_sal_per_dpt，用union合并后自然返回所有部门的数据，而非你需要的最高/最低薪资总和的部门。

方法一：通过子查询筛选极值对应的部门 #

with sum_sal_per_dpt as (
    select dpt_id, sum(salary) as total_salaries
    from salaries
    group by dpt_id
)
select dpt_id, total_salaries
from sum_sal_per_dpt
where total_salaries = (select max(total_salaries) from sum_sal_per_dpt)
   or total_salaries = (select min(total_salaries) from sum_sal_per_dpt);

方法二：使用窗口函数（推荐，支持并列极值场景） #

如果存在多个部门薪资总和同为最高/最低的情况，窗口函数能保留所有符合条件的部门：

with sum_sal_per_dpt as (
    select dpt_id, sum(salary) as total_salaries
    from salaries
    group by dpt_id
),
ranked_depts as (
    select dpt_id, total_salaries,
           rank() over(order by total_salaries desc) as rank_highest,
           rank() over(order by total_salaries asc) as rank_lowest
    from sum_sal_per_dpt
)
select dpt_id, total_salaries
from ranked_depts
where rank_highest = 1 or rank_lowest = 1;

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