mysql - 如何在已创建的字段上创建具有累积功能的 MySQL 查询？

Question

我有两个表，Invoice 和 Receiving，并且我正在使用 MySQL。我想从这两个表生成一个 Assets 负债表。结果应根据 Invoice 中的 I_Total 列和 Receiving 中的 CR_Amount 列随着时间的推移累积余额，如下所示如下图所示。我尝试了许多使用不同连接的查询，但没有得到所需的输出。

如何使用 MySQL 中的查询或函数获得所需的 Assets 负债表？

Answer 1

如果我们暂时忽略 balance 列，并假设日期列是 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 引入了窗口函数，这些函数已在 SQL Server 和 Oracle 等其他 RDBMS 中可用(称为“分析函数”)。

如果没有窗口函数，在 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