如何将指定SQL查询改写为派生表并解决字段无效报错问题

• 内层派生查询中使用了SUM()聚合函数，但没有添加对应的GROUP BY子句对YEAR(o.order_date)分组，导致非聚合字段order_date不符合SQL分组查询规范，触发报错。
• 目前你的改写逻辑和原始查询不一致：原始查询是统计每年的订单条目总数，你的改写变成了统计每年的销售额总和，两种场景的正确写法分别如下。

SELECT
    year,
    total
FROM 
    (SELECT
         YEAR(o.order_date) AS year,
         COUNT(i.order_id) AS total
     FROM 
         sales.order_items AS i
     INNER JOIN 
         sales.orders AS o ON i.order_id = o.order_id
     GROUP BY 
         YEAR(o.order_date)
    ) AS derived
ORDER BY year

内层已经按年分组完成聚合后，外层不需要再重复做GROUP BY和聚合，直接取字段即可：

SELECT
    time AS year,
    price AS total
FROM 
    (SELECT
         YEAR(o.order_date) AS time,
         SUM(i.quantity * i.list_price) AS price
     FROM 
         sales.order_items AS i
     INNER JOIN 
         sales.orders AS o ON i.order_id = o.order_id
     GROUP BY 
         YEAR(o.order_date)
    ) AS derived
ORDER BY year

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