如何创建一个 MySql 查询来显示多个客户有个人余额的贷方和借方的运行余额 [英] how to create a MySql query to display running balance from credit and debit where multiple customers have indivdual balances

问题描述

我有一张表，结构如下:在此处输入图片描述

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.

列表项是

1. 交易:

• 身份证
• 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; 

这篇关于如何创建一个 MySql 查询来显示多个客户有个人余额的贷方和借方的运行余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！