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

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

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

CLIENT_ID    ACTION_TYPE    ACTION_AMOUNT1            debit          10001            credit         1001            credit         5002            debit          10002            credit         12003            debit          10003            credit         10004            debit          1000

我的MySQL查询不起作用：

SELECT     clIEnt_ID,SUM(t_debits) AS debits,SUM(t_credits) AS credits,SUM(t_debits)-SUM(t_credits) AS balanceFROM table_name AS t_debitsleft JOIN table_name AS t_credits ON t_credits.clIEnt_ID=t_debits.clIEnt_IDWHERE     t_debits.action_type='debit'    AND t_credits.action_type='credit'    AND balance!=0GROUP BY t_debits.clIEnt_ID,t_credits.clIEnt_ID;

我期待的结果是：

CLIENT_ID    DEBITS    CREDITS    BALANCE1            1000      600        4002            1000      1200       -2004            1000      0          1000

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

DROP table IF EXISTS my_table;CREATE table my_table(transaction_ID INT NOT NulL auto_INCREMENT PRIMARY KEY,clIEnt_ID INT NOT NulL,action_type VARCHAR(12) NOT NulL,action_amount INT NOT NulL);INSERT INTO my_table(clIEnt_ID,action_type,action_amount) VALUES(1,'debit',1000),(1,'credit',100),500),(2,1200),(3,(4,1000);SELECT clIEnt_ID,SUM(COALESCE(CASE WHEN action_type = 'debit' THEN action_amount END,0)) total_debits,SUM(COALESCE(CASE WHEN action_type = 'credit' THEN action_amount END,0)) total_credits,0))      - SUM(COALESCE(CASE WHEN action_type = 'credit' THEN action_amount END,0)) balance   FROM my_table  GROUP      BY clIEnt_IDHAVING balance <> 0;+-----------+--------------+---------------+---------+| clIEnt_ID | total_debits | total_credits | balance |+-----------+--------------+---------------+---------+|         1 |         1000 |           600 |     400 ||         2 |         1000 |          1200 |    -200 ||         4 |         1000 |             0 |    1000 |+-----------+--------------+---------------+---------+

总结

以上是内存溢出为你收集整理的使用MySQL计算单个表中借方和贷方的余额全部内容，希望文章能够帮你解决使用MySQL计算单个表中借方和贷方的余额所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。