MySQL技术问询：合并借贷统计查询并实现金额相减

嘿，我明白你想把两个分开的统计查询合并，还得算出每个物业的贷方减借方的净额对吧？这其实有两种常用的方法，我给你详细讲讲：

方法一：条件聚合（推荐，效率更高） #

这种方法只需要扫描一次交易表，用条件判断来分别统计贷方和借方的总额，是最高效的方式。不同数据库的写法略有区别，但核心逻辑一致：

MySQL/MariaDB 写法 #

SELECT 
    property,
    SUM(CASE WHEN type = 'CREDIT' THEN amount ELSE 0 END) AS creditamount,
    SUM(CASE WHEN type = 'DEBIT' THEN amount ELSE 0 END) AS debitamount,
    SUM(CASE WHEN type = 'CREDIT' THEN amount ELSE -amount END) AS net_amount -- 直接算出净额
FROM transactions
GROUP BY property;

或者用MySQL特有的IF函数简化：

SELECT 
    property,
    SUM(IF(type = 'CREDIT', amount, 0)) AS creditamount,
    SUM(IF(type = 'DEBIT', amount, 0)) AS debitamount,
    SUM(IF(type = 'CREDIT', amount, -amount)) AS net_amount
FROM transactions
GROUP BY property;

通用SQL写法（适用于PostgreSQL、SQL Server等） #

SELECT 
    property,
    SUM(CASE WHEN type = 'CREDIT' THEN amount ELSE 0 END) AS creditamount,
    SUM(CASE WHEN type = 'DEBIT' THEN amount ELSE 0 END) AS debitamount,
    SUM(CASE WHEN type = 'CREDIT' THEN amount ELSE -amount END) AS net_amount
FROM transactions
GROUP BY property;

解释：通过CASE WHEN或者IF函数，只对符合类型的金额进行求和，不符合的就用0代替（或者在净额计算里用负的借方金额），最后按物业分组，一次查询就能拿到所有需要的数据。

方法二：LEFT JOIN 合并两个子查询 #

如果你更习惯分开统计再合并的思路，也可以用LEFT JOIN把两个子查询的结果关联起来，注意要处理某个物业只有贷方或只有借方的情况（避免NULL值影响计算）：

SELECT 
    COALESCE(c.property, d.property) AS property,
    COALESCE(c.creditamount, 0) AS creditamount,
    COALESCE(d.debitamount, 0) AS debitamount,
    COALESCE(c.creditamount, 0) - COALESCE(d.debitamount, 0) AS net_amount
FROM (
    SELECT property, SUM(amount) as creditamount 
    FROM transactions 
    WHERE type="CREDIT" 
    GROUP BY property
) c
LEFT JOIN (
    SELECT property, SUM(amount) as debitamount 
    FROM transactions 
    WHERE type="DEBIT" 
    GROUP BY property
) d ON c.property = d.property
UNION ALL
SELECT 
    d.property,
    0 AS creditamount,
    d.debitamount,
    -d.debitamount AS net_amount
FROM (
    SELECT property, SUM(amount) as debitamount 
    FROM transactions 
    WHERE type="DEBIT" 
    GROUP BY property
) d
LEFT JOIN (
    SELECT property, SUM(amount) as creditamount 
    FROM transactions 
    WHERE type="CREDIT" 
    GROUP BY property
) c ON d.property = c.property
WHERE c.property IS NULL;

或者用FULL JOIN（如果你的数据库支持，比如PostgreSQL、SQL Server）会更简洁：

SELECT 
    COALESCE(c.property, d.property) AS property,
    COALESCE(c.creditamount, 0) AS creditamount,
    COALESCE(d.debitamount, 0) AS debitamount,
    COALESCE(c.creditamount, 0) - COALESCE(d.debitamount, 0) AS net_amount
FROM (
    SELECT property, SUM(amount) as creditamount 
    FROM transactions 
    WHERE type="CREDIT" 
    GROUP BY property
) c
FULL JOIN (
    SELECT property, SUM(amount) as debitamount 
    FROM transactions 
    WHERE type="DEBIT" 
    GROUP BY property
) d ON c.property = d.property;

解释：COALESCE函数用来把NULL值替换成0，这样就算某个物业只有一种交易类型，计算净额时也不会出错。FULL JOIN会保留两个子查询里的所有物业，而LEFT JOIN加UNION ALL是为了兼容不支持FULL JOIN的数据库（比如MySQL）。

总结 #

• 优先用条件聚合的方法，因为只需要扫描一次表，性能更好，代码也更简洁。
• 如果必须用子查询合并，记得处理NULL值的情况，避免计算结果出现NULL。

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