如何创建一个 MySql 查询来显示多个客户有个人余额的贷方和借方的运行余额
我有一张表,结构如下:在此处输入图片描述
I have a table as with the structure below: enter image description here
该表是针对不同客户的简单贷记表和借记表(每个客户都有自己的 ID).
The Table is a simple credit and debit table for different customers ( each customer has his own ID).
交易发生在不同的日期.每笔交易都有自己的 ID,按时间顺序生成.
Transactions take place on different dates. Each transaction has its own ID which is chronologically generated.
必须创建一个视图来显示每个客户的运行余额.视图给出了按时间顺序排列的列表.
A view must be created as showing the running balance of each customer. The view gives the list chronologically arranged.
列表项是
- 交易:
- 身份证
- Customer_ID
- 日期
- 信用
- 借记和
- 余额(计算)
我想获取解决上述问题的查询代码.感谢您的期待.
I would like to get the query code for solving the above problem. Thanking you in anticipation.
推荐答案
这应该可行
select transaction_id, customer_id, date, credit, debit,
abs(sum(ifnull(credit,0)) over (partition by customer_id order by date,credit,transaction_id ) - sum(ifnull(debit,0)) over(partition by customer_id order by date,debit,transaction_id)) as balance
from ledger
order by transaction_id;
相关文章