如何将多子查询SQL语句优化为更少或无子查询的高效语句？

当然可以把这些重复的子查询合并成单次表扫描的查询，原SQL四次扫描同一张表，完全可以优化成只扫一次（甚至更少行数），大幅提升执行效率。

优化后的SQL（基础版） #

SELECT
    COALESCE(SUM(CASE WHEN buyer_number = 101 AND stock_id = 22 AND status = 'bought' THEN COALESCE(quantity, 0) ELSE 0 END), 0)
    - COALESCE(SUM(CASE WHEN seller_number = 101 AND stock_id = 22 AND status = 'sold' THEN COALESCE(quantity, 0) ELSE 0 END), 0) AS balance_primary,
    COALESCE(SUM(CASE WHEN buyer_number = 101 AND stock_id = 22 AND status = 'received' THEN COALESCE(quantity, 0) ELSE 0 END), 0)
    - COALESCE(SUM(CASE WHEN seller_number = 101 AND stock_id = 22 AND status = 'gifted' THEN COALESCE(quantity, 0) ELSE 0 END), 0) AS balance_secondary
FROM transaction;

进一步优化（减少扫描行数） #

如果表数据量很大，可以先过滤出仅和计算相关的行，再做聚合，效率会更高：

SELECT
    COALESCE(SUM(CASE WHEN status = 'bought' THEN COALESCE(quantity, 0) ELSE 0 END), 0)
    - COALESCE(SUM(CASE WHEN status = 'sold' THEN COALESCE(quantity, 0) ELSE 0 END), 0) AS balance_primary,
    COALESCE(SUM(CASE WHEN status = 'received' THEN COALESCE(quantity, 0) ELSE 0 END), 0)
    - COALESCE(SUM(CASE WHEN status = 'gifted' THEN COALESCE(quantity, 0) ELSE 0 END), 0) AS balance_secondary
FROM transaction
WHERE (buyer_number = 101 AND stock_id = 22 AND status IN ('bought', 'received'))
   OR (seller_number = 101 AND stock_id = 22 AND status IN ('sold', 'gifted'));

优化逻辑说明 #

• 原SQL的四个子查询各自独立扫描transaction表，总共四次全表扫描（如果没有合适索引的话）。
• 优化后的查询通过条件聚合（CASE WHEN + SUM），在单次表扫描中完成所有求和计算，再得到最终的差值，逻辑和原SQL完全一致，但执行开销大幅降低。
• 第二个版本通过WHERE子句提前过滤掉无关行，进一步减少需要处理的数据量，性能提升更明显。

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