mysql中计算金额_使用MySQL计算单个表中借方和贷方的余额

使用以下MySQL表包含带有相关金额的借方或贷方“操作”,如何选择具有非零“余额”的所有CLIENT_ID？我已经尝试将表加入到自身中以计算所有借方和贷方总计,但有些东西无法正常工作.

CLIENT_ID ACTION_TYPE ACTION_AMOUNT

1 debit 1000

1 credit 100

1 credit 500

2 debit 1000

2 credit 1200

3 debit 1000

3 credit 1000

4 debit 1000

我的MySQL查询不起作用：

SELECT

client_id,SUM(t_debits) AS debits,SUM(t_credits) AS credits,SUM(t_debits)-SUM(t_credits) AS balance

FROM table_name AS t_debits

LEFT JOIN table_name AS t_credits ON t_credits.client_id=t_debits.client_id

WHERE

t_debits.action_type='debit'

AND t_credits.action_type='credit'

AND balance!=0

GROUP BY t_debits.client_id,t_credits.client_id;

我期待的结果是：

CLIENT_ID DEBITS CREDITS BALANCE

1 1000 600 400

2 1000 1200 -200

4 1000 0 1000

我不知道还有什么可以尝试的.任何帮助都会很棒.