MySQL 与贷款相关的交易总金额，按相反顺序

Question

我正在尝试添加交易表中的金额，但添加操作必须以相反的顺序进行，并按交易所属的贷款进行分组。让我举一些例子:

Transactions
tid    loanid    amount    entrydate
------------------------------------
1      1         1,500     2013-06-01
2      2         1,500     2013-06-01
3      1         1,000     2013-06-02
4      3         2,300     2013-06-04
5      5         2,000     2013-06-04
6      1         1,100     2013-06-07
7      2         1,000     2013-06-09
|      |         |         |


Loans
loanid
------
1
2
3
4
5
|

如您所见，loanid 4 没有任何交易，只是为了明确一点，每笔贷款没有义务存在交易。

现在，我想要实现的是总结每笔贷款的交易金额。第一种方法实现了这一点:

SELECT tr.tid,
       l.loanid,
       tr.entrydate,
       tr.amount,
       @prevLoan:=l.loanid prevloan,
       @amnt:=if(@prevLoan:=l.loanid, @amnt+tr.amount, tr.amount) totAmnt

FROM (SELECT * FROM Transactions) tr

JOIN (SELECT @prevLoan:=0, @amnt:=0) t

JOIN Loans l
     ON l.loanid = tr.loanid


GROUP BY l.loanid, tr.tid

它实现了这样的效果:

tid    loanid    entrydate    amount    prevloan    totAmnt
-----------------------------------------------------------
1      1         2013-06-01   1,500     1           1,500
3      1         2013-06-02   1,000     1           2,500
6      1         2013-06-07   1,100     1           3,600  <-- final result for loanid=1
2      2         2013-06-01   1,500     2           1,500
7      2         2013-06-09   1,000     2           2,500  <-- final result for loanid=2
4      3         2013-06-04   2,300     3           2,300  <-- final result for loanid=3
5      5         2013-06-04   2,000     5           2,000  <-- final result for loanid=5
|      |         |            |         |           |

正如您所看到的，对于每笔贷款，属于该贷款的交易金额都会在 totAmnt 列中求和，因此每笔贷款的最后一笔交易具有同一笔贷款的交易总和。

现在......我真正需要的是以相反的顺序完成总和。我的意思是，对于每笔贷款的相同交易，总和仍然得到相同的结果，但我需要从每笔贷款的最后一笔交易到第一笔交易完成总和。

我已尝试以下操作，但无济于事(它与上一个查询相同，但在 FROM transactions 表上使用 Order By DESC):

SELECT tr.tid,
       l.loanid,
       tr.entrydate,
       tr.amount,
       @prevLoan:=l.loanid prevloan,
       @amnt:=if(@prevLoan:=l.loanid, @amnt+tr.amount, tr.amount) totAmnt

FROM (SELECT * FROM Transactions ORDER BY tr.entrydate DESC) tr

JOIN (SELECT @prevLoan:=0, @amnt:=0) t

JOIN Loans l
     ON l.loanid = tr.loanid


GROUP BY l.loanid, tr.tid

我使用 tr.entrydate 是因为这是一种更熟悉的表示订单标准的方式，除此之外，政策所说的是有效的订单标准，tid 可能会说一些东西，但 Entrydate 是交易表的排序列。 .

使用上一个查询，我只得到与第一个查询相同的结果，所以我猜那里肯定缺少一些东西。我需要的是得到如下结果:

tid    loanid    entrydate    amount    prevloan    totAmnt
-----------------------------------------------------------
6      1         2013-06-07   1,100     1           1,100
3      1         2013-06-02   1,000     1           2,100
1      1         2013-06-01   1,500     1           3,600  <-- final result for loanid=1
7      2         2013-06-09   1,000     2           1,000
2      2         2013-06-01   1,500     2           2,500  <-- final result for loanid=2
4      3         2013-06-04   2,300     3           2,300  <-- final result for loanid=3
5      5         2013-06-04   2,000     5           2,000  <-- final result for loanid=5
|      |         |            |         |           |

正如您所看到的，每个贷款 ID 的总和得到相同的最终结果，但交易的总和是以相反的顺序完成的......

希望所有这些困惑都清楚......我怎样才能达到这样的结果？

Answer 1

你看起来非常接近......我认为你有两个小调整。首先，不要使用外部 GROUP BY，因为您没有进行任何聚合(sum、min、max、avg 等)。其次，在查询交易表时，只需先按贷款 ID 排序，然后按日期降序排列...这样，所有贷款 ID 都会按正确的顺序分组在一起，但在每笔贷款中，它们会按您的降序排列寻找。另外，在累积后调整您的@prevLoan，以便可以将当前记录与下一个记录进行比较。您将@variable 设置为零，因此无论如何它都不会与第一次运行时的第一个贷款ID 匹配。最后，您甚至不需要连接到贷款表，因为交易表具有贷款 ID 可以用作比较测试的基础。由于最里面的查询按贷款排序，然后输入日期降序排列，因此您不应在外部查询中再次需要它。

SELECT 
      tr.tid,
      tr.loanid,
      tr.entrydate,
      tr.amount,
      @amnt := if( @prevLoan = tr.loanid, @amnt+tr.amount, tr.amount) totAmnt,
      @prevLoan := tr.loanid prevloan
   FROM 
      ( SELECT * 
           FROM Transactions 
           ORDER BY loanid, entrydate DESC) tr

      JOIN (SELECT @prevLoan := 0, 
                   @amnt := 0) t

替代解决方案？根据我的评论，看起来你想要高总数并缩小......这更接近吗？

SELECT 
      tr.tid,
      tr.loanid,
      tr.entrydate,
      tr.amount,
      trTotals.TotalLoans - if( @prevLoan = tr.loanid, @amnt, 0 ) as NewBal,
      @amnt := if( @prevLoan = tr.loanid, @amnt+tr.amount, tr.amount) runningTotals,
      @prevLoan := tr.loanid prevloan
   FROM 
      ( SELECT * 
           FROM Transactions 
           ORDER BY loanid, entrydate DESC) tr

      JOIN ( SELECT loanid, sum( amount ) as TotalLoans
                FROM Transactions 
                group by loanid ) trTotals
         on tr.loanid = trTotals.loanid

      JOIN (SELECT @prevLoan := 0, 
                   @amnt := 0) t

Produces...                      (Total Paid)   (for reversing calc)
tid   loanid  entrydate    amount   NewBal      Running Totals   prevLoan
6     1       2013-06-07   1100     3600        1100             1
3     1       2013-06-02   1000     2500        2100             1
1     1       2013-06-01   1500     1500        3600             1
7     2       2013-06-09   1000     2500        1000             2
2     2       2013-06-01   1500     1500        2500             2
4     3       2013-06-04   2300     2300        2300             3
5     5       2013-06-04   2000     2000        2000             5