如何创建一个 MySql 查询来显示多个客户有个人余额的贷方和借方的运行余额

2021-09-17 00:00:00 mysql view calculation bank

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

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;

相关文章