如何在SQL中结合使用SUM与MAX函数？附查询需求及问题

问题背景 #

现有如下SQL查询，用于统计每个流派对应不同国家的交易数据：

SELECT DISTINCT 
    G.NAME AS GENRE, 
    COUNT(T.NAME) AS TotalTransactions, 
    f.country, SUM(il.unitprice) AS Spent
FROM 
    final F
JOIN 
    CUSTOMER C ON C.CUSTOMERID = F.CUSTOMERID
JOIN 
    INVOICE I ON I.CUSTOMERID = C.CUSTOMERID
JOIN 
    INVOICELINE IL ON IL.INVOICEID = I.INVOICEID
JOIN 
    TRACK T ON T.TRACKID = IL.TRACKID
JOIN 
    GENRE G ON G.GENREID = T.GENREID
GROUP BY 
    G.NAME, c.country
ORDER BY 
    G.NAME;

需要进一步筛选出每个流派中消费金额（Spent）最高的国家记录，期望输出示例：

GENRE           TotalTransactions   Country   Spent
 ----------------------------------------------------
 Alternative              5          USA         4.95
 Alternative & Punk      36          Canada     35.64

尝试使用MAX(SUM())时出现分组错误，以下是可行的解决方法：

方法一：使用窗口函数（推荐） #

利用ROW_NUMBER()窗口函数，先计算流派-国家的聚合数据，再给每个流派内的记录按消费金额降序排名，取排名第一的记录：

WITH GenreCountryStats AS (
    SELECT 
        G.NAME AS GENRE, 
        COUNT(T.NAME) AS TotalTransactions, 
        C.country, 
        SUM(IL.unitprice) AS Spent
    FROM 
        final F
    JOIN 
        CUSTOMER C ON C.CUSTOMERID = F.CUSTOMERID
    JOIN 
        INVOICE I ON I.CUSTOMERID = C.CUSTOMERID
    JOIN 
        INVOICELINE IL ON IL.INVOICEID = I.INVOICEID
    JOIN 
        TRACK T ON T.TRACKID = IL.TRACKID
    JOIN 
        GENRE G ON G.GENREID = T.GENREID
    GROUP BY 
        G.NAME, C.country
)
SELECT GENRE, TotalTransactions, country, Spent
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY GENRE ORDER BY Spent DESC) AS rn
    FROM GenreCountryStats
) t
WHERE rn = 1
ORDER BY GENRE;

• 说明：如果有多个国家在同一流派中消费金额并列最高，ROW_NUMBER()会仅保留其中一条；若需保留所有并列记录，可替换为RANK()或DENSE_RANK()。

方法二：子查询关联（兼容旧版数据库） #

如果你的数据库不支持窗口函数，可先计算每个流派的最高消费金额，再关联回原始聚合数据：

-- 第一步：计算每个流派-国家的聚合数据
WITH GenreCountryStats AS (
    SELECT 
        G.NAME AS GENRE, 
        COUNT(T.NAME) AS TotalTransactions, 
        C.country, 
        SUM(IL.unitprice) AS Spent
    FROM 
        final F
    JOIN 
        CUSTOMER C ON C.CUSTOMERID = F.CUSTOMERID
    JOIN 
        INVOICE I ON I.CUSTOMERID = C.CUSTOMERID
    JOIN 
        INVOICELINE IL ON IL.INVOICEID = I.INVOICEID
    JOIN 
        TRACK T ON T.TRACKID = IL.TRACKID
    JOIN 
        GENRE G ON G.GENREID = T.GENREID
    GROUP BY 
        G.NAME, C.country
),
-- 第二步：计算每个流派的最高消费金额
GenreMaxSpent AS (
    SELECT 
        GENRE,
        MAX(Spent) AS MaxSpent
    FROM GenreCountryStats
    GROUP BY GENRE
)
-- 第三步：关联筛选出符合条件的记录
SELECT 
    gcs.GENRE, 
    gcs.TotalTransactions, 
    gcs.country, 
    gcs.Spent
FROM GenreCountryStats gcs
JOIN GenreMaxSpent gms ON gcs.GENRE = gms.GENRE AND gcs.Spent = gms.MaxSpent
ORDER BY gcs.GENRE;

• 说明：该方法会保留同一流派中所有消费金额并列最高的国家记录。

为什么MAX(SUM())会报错？ #

SQL不允许聚合函数嵌套使用，SUM(il.unitprice)是基于G.NAME, C.country分组后的计算结果，而MAX()需要在更高层级的分组上执行，直接写MAX(SUM(il.unitprice))会导致分组逻辑冲突，因此无法解析。

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