SQL先使用SUM后用MAX函数查询每年销量最高车型的实现方法

• 内层子查询的sum(sold)没有设置别名，外层查询无法识别sold字段
• 外层查询未按year分组，即便补充分组后，也无法直接关联到最高销量对应的车型，无法得到预期结果

方案1：窗口函数实现（支持MySQL8.0+、PostgreSQL、Oracle等主流新版数据库） #

该方案逻辑清晰，也支持同一年份多车型销量并列第一的场景：

WITH car_year_total AS (
    -- 第一步：按车型、年份统计总销量
    SELECT car, year, SUM(sold) AS total_sold
    FROM sales
    GROUP BY car, year
),
sales_rank AS (
    -- 第二步：按年份分组，给同一年的车型按总销量降序排名
    SELECT 
        car, year, total_sold,
        RANK() OVER(PARTITION BY year ORDER BY total_sold DESC) AS rk
    FROM car_year_total
)
-- 第三步：取每个年份排名第一的记录
SELECT car, year, total_sold AS sold
FROM sales_rank
WHERE rk = 1;

方案2：关联子查询实现（兼容MySQL5.x等不支持窗口函数的旧版数据库） #

SELECT t1.car, t1.year, t1.total_sold AS sold
FROM (
    SELECT car, year, SUM(sold) AS total_sold
    FROM sales
    GROUP BY car, year
) t1
INNER JOIN (
    -- 先查询每个年份的最高总销量
    SELECT year, MAX(total_sold) AS max_sold
    FROM (
        SELECT car, year, SUM(sold) AS total_sold
        FROM sales
        GROUP BY car, year
    ) t
    GROUP BY year
) t2 ON t1.year = t2.year AND t1.total_sold = t2.max_sold;

以上两种方案执行后都会得到你给出的预期结果。

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