主页 / dba / 问题 / 214108
Accepted
FarukWeb
Asked: 2018-08-06 12:54:03 +0800 CST

如何从 MySQL 中的两个表中计算余额

  • 772

我有两个表如下：

tbl_credit

id
cr_amount
client_id

tbl_debit

id
dr_amount
client_id

我想计算总数sum(cr_amount)-total(dr_amount)where group by client_id。

mysql sum

2 个回答

  1. union 可能效果最好，这是一个粗略的例子

    SELECT clientid,SUM(balance) FROM
    (SELECT clientid,cramount as balance FROM A
       UNION ALL
    SELECT clientid,- dramount as balance FROM B
   )
  GROUP BY clientid
    • 3
  2. Best Answer

    我已经编辑了 sql ，请试试这个，让我知道谢谢。

    SELECT (credit - debit) as balance 
     FROM ( 
    (SELECT SUM(cr_amount) as credit,client_id FROM tbl_credit GROUP BY client_id) as credit
       LEFT JOIN 
    (SELECT SUM(dr_amount) as debit,client_id FROM tbl_debit GROUP BY client_id) as debit 
     ON debit.client_id = credit.client_id)
    • 1

相关问题