SQL技术问询：如何基于双表计算总和并做差，筛选余额超£5000的客户

需求说明 #

我们需要从Customer表和Transactions表中筛选出当前余额超过£5000的客户，余额计算规则为：

当前余额 = 账户初始余额（opening_balance） + 所有入账交易总额 - 所有出账交易总额

解决方案1：单查询聚合计算 #

这是最直接的写法，通过左关联两张表，用条件聚合分别统计入账、出账总额，再计算最终余额并筛选：

SELECT 
    c.customer_id,
    c.customer_name,
    c.opening_balance + COALESCE(SUM(CASE WHEN t.transaction_type = 'incoming' THEN t.amount ELSE 0 END), 0) - COALESCE(SUM(CASE WHEN t.transaction_type = 'outgoing' THEN t.amount ELSE 0 END), 0) AS current_balance
FROM 
    Customer c
LEFT JOIN 
    Transactions t ON c.customer_id = t.customer_id
GROUP BY 
    c.customer_id, c.customer_name, c.opening_balance
HAVING 
    current_balance > 5000;

• 使用LEFT JOIN确保没有交易记录的客户也能被纳入计算
• COALESCE用来处理无交易时SUM返回NULL的情况，避免计算出错
• CASE语句分别归类统计入账、出账金额，最后聚合后用HAVING筛选余额达标的客户

针对你的疑问：可以分别计算总和再做加减 #

完全可以先单独计算入账、出账的总额表，再关联Customer表完成余额计算。下面用CTE（公共表表达式）实现这种方式：

-- 统计每个客户的入账交易总额
WITH IncomingTotals AS (
    SELECT 
        customer_id,
        SUM(amount) AS total_incoming
    FROM 
        Transactions
    WHERE 
        transaction_type = 'incoming'
    GROUP BY 
        customer_id
),
-- 统计每个客户的出账交易总额
OutgoingTotals AS (
    SELECT 
        customer_id,
        SUM(amount) AS total_outgoing
    FROM 
        Transactions
    WHERE 
        transaction_type = 'outgoing'
    GROUP BY 
        customer_id
)
-- 关联客户表计算余额并筛选
SELECT 
    c.customer_id,
    c.customer_name,
    c.opening_balance + COALESCE(it.total_incoming, 0) - COALESCE(ot.total_outgoing, 0) AS current_balance
FROM 
    Customer c
LEFT JOIN 
    IncomingTotals it ON c.customer_id = it.customer_id
LEFT JOIN 
    OutgoingTotals ot ON c.customer_id = ot.customer_id
WHERE 
    c.opening_balance + COALESCE(it.total_incoming, 0) - COALESCE(ot.total_outgoing, 0) > 5000;

这种方式先拆分出两个独立的总额统计结果，再和客户表关联计算，逻辑清晰，完全符合你“计算两个不同的总和，再从其中一个表的列数值中做减法运算”的需求。

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