如何修正SQL查询，筛选订单总额超过存款总额的用户

云原生阿岚

2026-8-18

问题分析与修正

原查询存在两个核心问题：

WHERE子句无法引用聚合别名：total_disposits和spent是聚合计算后的别名，WHERE在GROUP BY之前执行，此时聚合结果还未生成，根本无法识别这些别名，必须改用HAVING子句或者提前计算聚合值。
多表直接关联导致重复计算：TRANSACTIONS和ORDERS是与USERS分别建立一对多的关系，直接关联会产生笛卡尔积——比如用户1有2笔交易、2笔订单，关联后会生成4条重复记录，sum时会把交易金额和订单金额各重复计算2次，最终得到的总额完全失真。

修正后的SQL写法

先分别计算每个用户的存款总额和订单总额，再关联用户表进行筛选，避免笛卡尔积问题：

SELECT 
    u.id, 
    u.email, 
    u.balance, 
    COALESCE(t.total_disposits, 0) AS total_disposits, 
    COALESCE(o.spent, 0) AS spent
FROM USERS u
LEFT JOIN (
    SELECT 
        user_id, 
        SUM(amount - fees) AS total_disposits
    FROM TRANSACTIONS
    GROUP BY user_id
) t ON u.id = t.user_id
LEFT JOIN (
    SELECT 
        user_id, 
        SUM(charge) AS spent
    FROM ORDERS
    GROUP BY user_id
) o ON u.id = o.user_id
WHERE COALESCE(o.spent, 0) > COALESCE(t.total_disposits, 0);

如果确定所有用户都至少有一笔交易和订单，也可以用更简洁的方式：

SELECT 
    u.id, 
    u.email, 
    u.balance, 
    t.total_disposits, 
    o.spent
FROM USERS u
INNER JOIN (SELECT user_id, SUM(amount - fees) AS total_disposits FROM TRANSACTIONS GROUP BY user_id) t ON u.id = t.user_id
INNER JOIN (SELECT user_id, SUM(charge) AS spent FROM ORDERS GROUP BY user_id) o ON u.id = o.user_id
WHERE o.spent > t.total_disposits;