时间戳

首页 > 解决方案 > 如何在创建的字段上创建累积的 MySQL 查询？

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

问题描述

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

在此处输入图像描述

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

标签： mysqlsql

解决方案

如果我们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

推荐阅读