如何优化50个银行账户余额求和的SQL查询？替代Union拼接的高效写法

嘿，这个写法确实有点笨重，50个UNION子查询不仅写起来费劲儿，执行效率也拉胯——数据库得反复扫描accounts表50次，还要处理UNION默认的去重逻辑（哪怕你不需要去重，这也是额外开销）。给你两种更高效的优化思路，代码简洁还能大幅提升执行速度：

方案1：用JOIN关联临时账户-日期数据集 #

直接构造一个包含所有目标账户和对应截止日期的临时数据集，然后和accounts表做一次关联查询，只需要扫描主表一次：

SELECT SUM(a.balance)
FROM accounts a
JOIN (
    VALUES
        ('B1', '2024-01-01'), -- 替换成实际的D1日期值，格式要和lastModifiedDate匹配
        ('B2', '2024-01-02'),
        -- ... 依次添加到B50和对应的D50
        ('B50', '2024-02-18')
) AS account_dates(address, cutoff_date)
    ON a.address = account_dates.address
    AND a.lastModifiedDate > account_dates.cutoff_date;

方案2：用CTE（公共表表达式）提升可读性 #

如果你的数据库支持CTE（比如MySQL 8.0+、PostgreSQL、SQL Server等），可以把账户-日期集合单独定义，代码可读性更好：

WITH account_dates AS (
    SELECT 'B1' AS address, '2024-01-01'::DATE AS cutoff_date UNION ALL
    SELECT 'B2' AS address, '2024-01-02'::DATE AS cutoff_date UNION ALL
    -- ... 依次添加到B50和对应的D50
    SELECT 'B50' AS address, '2024-02-18'::DATE AS cutoff_date
)
SELECT SUM(a.balance)
FROM accounts a
JOIN account_dates ad
    ON a.address = ad.address
    AND a.lastModifiedDate > ad.cutoff_date;

为什么这两种方法更高效？ #

• 减少表扫描次数：原来的写法要扫描accounts表50次，优化后只需要扫描1次，关联的是内存中的小数据集，执行计划更高效。
• 避免冗余逻辑：去掉了UNION的去重开销（如果原来用的是UNION ALL，虽然没有去重，但多次子查询的开销依然存在）。
• 更易维护：要新增/删除账户，只需要修改临时数据集里的行，不用重复写大量相同的子查询。

注意：一定要确保cutoff_date的格式和lastModifiedDate的字段类型匹配（比如是DATE还是DATETIME），避免类型转换导致的性能问题。

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