使用SELECT MAX()时多列查询引发ORA-00937错误的问题咨询

问题背景 #

我有一张SALES表，先通过子查询按国家分组计算出每个国家的销售总额：

select sum(SP * QTY_SOLD) as SalesAmt, Country 
from SALES 
group by COUNTRY
order by SalesAmt desc

执行Query1可以正常返回所有国家中的最大销售总额：

select 
    max(SalesAmt)
from
    (select sum(SP * QTY_SOLD) as SalesAmt, Country 
     from SALES 
     group by COUNTRY
     order by SalesAmt desc)

但执行Query2时抛出错误：

ORA-00937: not a single-group group function
00937. 00000 - "not a single-group group function"

Query2的语句是：

select max(SalesAmt), Country 
from
    (select sum(SP * QTY_SOLD) as SalesAmt, Country 
     from SALES 
     group by COUNTRY
     order by SalesAmt desc)

我认为子查询包含这两列，Query2应该返回对应的结果，为什么会报错？

错误原因 #

Oracle SQL有个硬性规则：当你在SELECT列表里同时使用聚合函数（比如max()）和非聚合列（比如Country）时，非聚合列必须满足以下两个条件之一：

• 出现在GROUP BY子句中，作为分组依据；
• 被另一个聚合函数包裹（比如max(Country)，但这在业务场景下通常没有意义）。

在Query2中，你既没有将Country加入GROUP BY作为分组条件，也没有用聚合函数处理它。数据库无法确定要将单一的max(SalesAmt)值与哪个Country关联——子查询返回的是多个国家的销售数据，数据库不知道你想要配对的是哪个国家，因此直接抛出错误。

而Query1只选择了聚合函数max(SalesAmt)，没有非聚合列，数据库会将整个子查询的结果视为一个单独的分组来计算最大值，所以可以正常执行。

解决方法 #

如果你想要获取销售总额最高的国家及其对应销售额，可以用以下两种常见写法：

方法1：用rownum取排序后的第一条数据 #

select SalesAmt, Country
from (
    select sum(SP * QTY_SOLD) as SalesAmt, Country 
    from SALES 
    group by COUNTRY
    order by SalesAmt desc
)
where rownum = 1;

注意：如果有多个国家的销售总额并列最高，这种方法只会返回其中一个。

方法2：用窗口函数处理并列情况 #

如果需要返回所有销售总额并列最高的国家，可以用RANK()窗口函数：

select SalesAmt, Country
from (
    select sum(SP * QTY_SOLD) as SalesAmt, Country,
           rank() over(order by sum(SP * QTY_SOLD) desc) as rank_num
    from SALES 
    group by COUNTRY
)
where rank_num = 1;

这种写法会把所有销售额排名第一的国家都查出来。

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