如何在创建的字段上累积创建MySQL查询？

Question

我有两个桌子，Invoice和Receiving，我使用的是MySQL。我想从这两张表中生成一份资产负债表。结果应该根据来自Invoice的Invoice列和来自Receiving的CR_Amount列积累平衡，如下图所示。我尝试过使用不同联接的许多查询，但没有得到所需的输出。

​

​

如何使用MySQL中的查询或函数实现所需的资产负债表？

Answer 1

如果我们暂时忽略balance列，并假设date列是DATE数据类型，那么就可以通过这样的方法获得显示的结果：

SELECT t.date
     , t.debit
     , t.credit
  FROM ( SELECT i.i_date       AS date
              , i.i_total      AS debit
              , 0              AS credit
              , 'i'            AS i_or_r
              , i.i_id         AS id
           FROM invoice i
          UNION ALL
         SELECT r.r_date       AS date
              , 0              AS debit
              , r.r_total      AS credit
              , 'r'            AS i_or_r
              , r.r_id         AS id
           FROM receiving r
       ) t
 ORDER
    BY t.date
     , t.i_or_r
     , t.id

为了获得平衡，我们可以在客户端进行处理，因为行是被检索的。

注意: MySQL 8.0引入了窗口函数，这些函数在其他关系数据库管理系统中是可用的，比如Server和Oracle (称之为“分析函数”)。

如果没有窗口函数，在SQL中完成它将是很糟糕的。

我们可以使用用户定义变量的不受支持的用法。使用这种方法，我们将基本上模拟在客户端进行的处理，获取查询的结果(按顺序处理每一行)，以从用户定义的变量中的“运行余额”中添加/减去。这件事的“丑陋”之处在于，它依赖于无法保证的行为。)“MySQL参考手册”包括对此的警告。)

或者，为了使用纯SQL获得结果，我们可以使用两个复杂的看起来相关的子查询来总结到当前行的借方和贷方金额，并对每一行都这样做。

看起来，我们用借方和贷方来处理余额，与银行的顺序相似，按日期顺序使用所有的借方和贷方。在每个日期，我们首先使用借方，然后是贷项。

从样本数据和预期结果来看，尚不清楚借方是按升序按金额排序，还是按升序按id排序。

对于样本数据，无论哪种方法，我们都得到了相同的平衡结果。假设i_id在发票中是唯一的，而cr_id在接收方面是唯一的，那么当我们在当前日期时，我们可以使用id订单作为判别器应用贷项和借方来获得余额。

(如果我们需要按金额递增顺序在同一日期应用贷项，则子查询将更加复杂，以考虑到同一日期的两个学分可能用于相同的金额。)

SELECT t.date
     , t.debit
     , t.credit

     , ( SELECT SUM(bi.i_total)
           FROM invoice bi
          WHERE bi.i_date <= t.date
            AND ( bi.i_date < t.date
                OR ( t.i_or_r = 'i' AND bi.i_id <= t.id )
                )
       )
     - ( SELECT SUM(br.cr_amount)
           FROM receiving br
          WHERE br.cr_date <= t.date
            AND ( br.cr_date < t.date
                OR ( t.i_or_r = 'r' AND br.cr_id <= t.id )
                OR t.i_or_r = 'i'
                )
       ) AS balance

  FROM ( SELECT i.i_date       AS date
              , i.i_total      AS debit
              , 0              AS credit
              , 'i'            AS i_or_r
              , i.i_id         AS id
           FROM invoice i
          UNION ALL
         SELECT r.cr_date      AS date
              , 0              AS debit
              , r.cr_amount    AS credit
              , 'r'            AS i_or_r
              , r.cr_id        AS id
           FROM receiving r
       ) t
 ORDER
    BY t.date
     , t.i_or_r
     , t.id

Answer 2

尝尝这个

SELECT *, SUM(Debit) OVER(ORDER BY dt,debit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)-SUM(Credit) OVER(ORDER BY dt,debit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Balance
FROM (
SELECT ID,Dt,Amount AS Debit,0 AS Credit FROM Debit
UNION ALL
SELECT ID,Dt,0 AS Debit,Amount AS Credit FROM Credit
)X 
ORDER BY DT

演示