如何实现同一表中不同条件下sum结果的算术运算？

原SQL存在语法错误：子查询返回多列却未对应到单独的运算项，且括号匹配逻辑混乱，无法正常执行。以下是两种可行的正确写法：

方法一：条件聚合（推荐，性能更优） #

只需要扫描一次表，通过CASE WHEN筛选不同条件的行分别求和，再直接执行算术运算：

SELECT
    SUM(CASE WHEN id = 1 THEN amount ELSE 0 END) - SUM(CASE WHEN id = 2 THEN amount ELSE 0 END) AS amount_diff,
    SUM(CASE WHEN id = 1 THEN qty ELSE 0 END) - SUM(CASE WHEN id = 2 THEN qty ELSE 0 END) AS qty_diff
FROM table1;

如果要避免某条件无匹配行时返回NULL，可以用COALESCE将NULL转为0：

SELECT
    COALESCE(SUM(CASE WHEN id = 1 THEN amount ELSE 0 END), 0) - COALESCE(SUM(CASE WHEN id = 2 THEN amount ELSE 0 END), 0) AS amount_diff,
    COALESCE(SUM(CASE WHEN id = 1 THEN qty ELSE 0 END), 0) - COALESCE(SUM(CASE WHEN id = 2 THEN qty ELSE 0 END), 0) AS qty_diff
FROM table1;

方法二：标量子查询（逻辑直观） #

每个子查询仅返回单个聚合值，直接参与算术运算，适合条件复杂的场景：

SELECT
    (SELECT SUM(amount) FROM table1 WHERE id = 1) - (SELECT SUM(amount) FROM table1 WHERE id = 2) AS amount_diff,
    (SELECT SUM(qty) FROM table1 WHERE id = 1) - (SELECT SUM(qty) FROM table1 WHERE id = 2) AS qty_diff;

同样可以用COALESCE处理NULL：

SELECT
    COALESCE((SELECT SUM(amount) FROM table1 WHERE id = 1), 0) - COALESCE((SELECT SUM(amount) FROM table1 WHERE id = 2), 0) AS amount_diff,
    COALESCE((SELECT SUM(qty) FROM table1 WHERE id = 1), 0) - COALESCE((SELECT SUM(qty) FROM table1 WHERE id = 2), 0) AS qty_diff;

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