使用MySQL在单个表中根据借方和贷方计算余额 [英] Using MySQL to calculate balances from debits and credits in a single table
本文介绍了使用MySQL在单个表中根据借方和贷方计算余额的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在下面的MySQL表中包含具有相关金额的借方或贷方操作"时,如何选择所有余额"为非零的CLIENT_ID?我尝试将表格与自身连接起来以计算所有借方和贷方总计,但是某些方法无法正常工作.
With the following MySQL table containing debit or credit "actions" with associated amounts, how is it possible to select all CLIENT_IDs with a non-zero "balance"? I have tried joining the table to itself in order to calculate all debit and credit totals, but something isn't working correctly.
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查询无效:
My MySQL query that doesn't work:
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;
我期望的结果是这样的:
The result I am expecting is something like:
CLIENT_ID DEBITS CREDITS BALANCE
1 1000 600 400
2 1000 1200 -200
4 1000 0 1000
我不知道还有什么尝试.任何帮助都会很棒.
I have no idea what else to try. Any help would be great.
推荐答案
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),
(1 ,'credit', 500),
(2 ,'debit', 1000),
(2 ,'credit', 1200),
(3 ,'debit', 1000),
(3 ,'credit', 1000),
(4 ,'debit', 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
, SUM(COALESCE(CASE WHEN action_type = 'debit' THEN action_amount END,0))
- SUM(COALESCE(CASE WHEN action_type = 'credit' THEN action_amount END,0)) balance
FROM my_table
GROUP
BY client_id
HAVING balance <> 0;
+-----------+--------------+---------------+---------+
| client_id | total_debits | total_credits | balance |
+-----------+--------------+---------------+---------+
| 1 | 1000 | 600 | 400 |
| 2 | 1000 | 1200 | -200 |
| 4 | 1000 | 0 | 1000 |
+-----------+--------------+---------------+---------+
这篇关于使用MySQL在单个表中根据借方和贷方计算余额的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文