如何新增标签列标记两个PID的sum(amount)哪个更高？

你的原SQL存在两处关键问题：

• SELECT子句中，sum(s.amount) as 'Total Sales' 和CASE语句之间缺少逗号，会触发语法错误
• 子查询中错误使用了主查询的表别名s，子查询是独立执行的，应直接引用sales.pid而非s.pid

另外重复执行子查询会降低效率，下面提供两种优化/修正后的方案：

方案一：使用CTE优化查询（推荐） #

通过CTE一次性计算两个产品的总销售额，避免重复扫描sales表：

WITH product_totals AS (
    SELECT 
        p.pid,
        p.product,
        SUM(s.amount) AS total_sales
    FROM sales s
    JOIN products p ON s.pid = p.pid
    WHERE p.pid IN ('P01', 'P06')
    GROUP BY p.pid, p.product
)
SELECT 
    pt.product,
    pt.total_sales AS 'Total Sales',
    CASE 
        WHEN (SELECT total_sales FROM product_totals WHERE pid = 'P01') > (SELECT total_sales FROM product_totals WHERE pid = 'P06')
            THEN 'Milk is higher'
        WHEN (SELECT total_sales FROM product_totals WHERE pid = 'P06') > (SELECT total_sales FROM product_totals WHERE pid = 'P01')
            THEN 'Eclairs is higher'
        ELSE 'Equal'
    END AS WhichIsHigher
FROM product_totals pt;

方案二：直接修正语法错误 #

如果你的数据库不支持CTE，可直接修正原语句的语法问题，同时补充销售额相等的分支：

SELECT 
    p.product,
    SUM(s.amount) AS 'Total Sales',
    CASE 
        WHEN (SELECT SUM(amount) FROM sales WHERE pid = 'P01') > (SELECT SUM(amount) FROM sales WHERE pid = 'P06')
            THEN 'Milk is higher'
        WHEN (SELECT SUM(amount) FROM sales WHERE pid = 'P06') > (SELECT SUM(amount) FROM sales WHERE pid = 'P01')
            THEN 'Eclairs is higher'
        ELSE 'Equal'
    END AS WhichIsHigher
FROM sales s
JOIN products p ON s.pid = p.pid
WHERE p.pid IN ('P01', 'P06')
GROUP BY p.pid, p.product;

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