MYSQL如何查询当前余额作为以前的余额 [英] MYSQL how to query out current balance as previous balance

问题描述

我需要使用以下预期结果进行查询.考虑上一个.余额是自动计算的字段.我想通过获取余额(从前一行)来填充 MYSQL 查询，请参见图片.如何使用mysql查询出来?日期可以是任何不连续的日期，以下只是一个说明.

I need to make a query with the expected result as below. Consider the prev. Balance is auto-calculated field. And i want to populate with MYSQL query by taking the Balance (from previous row) see picture. How to query out using mysql? The date can be any date not sequential and that below is just an illustration.

请注意，之前的余额只是旧余额的序列.这意味着前一个余额始终为空或为零，并且余额从添加的金额中获取其金额.从第一个余额开始，第二个记录开始拥有之前的余额，然后继续

Note that the previous balance is just a sequence of the old balance. Meaning that the first previous balance always null or zero and the balance get its amount from added amount. From the first balance the second record start to have its previous balance and it goes on and on

推荐答案

我觉得我想得太远了....回过头来看这个查询很简单，只是这个想法在最初很难掌握地方.此查询将解决问题并计算余额和以前的余额.它称为运行余额/累积余额.这个想法是内部查询会先计算之前的余额......一旦获得了之前的余额，然后使用它来计算外部查询中的余额

I feel like i have thought this too far....after looking back the query is quite simple it's just that the idea was difficult to grasp in the first place. This query will solve the problem and calculate both balance and previous balance. It it called running balance / cumulative balance. The idea is the inner query will calculate the previous balance first...once the previous balance is obtain then use it to calculate balance in the outer query

SELECT 
  t2.date,
  t2.added_amount,
  t2.previous_balance
  t2.previous_balance + t2.added_amount AS balance

FROM      

(Select 
   t.date,
   t.added_amount,
   (SELECT sum(t1.added_amount) FROM table t1
    WHERE t1.date < t.date) as previous_balance
FROM table t
   ORDER BY t.date) t2

ORDER BY t2.date

这篇关于MYSQL如何查询当前余额作为以前的余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！