时间戳

首页 > 解决方案 > SQL会计系统中的分层汇总

mysql - SQL会计系统中的分层汇总

问题描述

我正在尝试从会计系统中的一般日记账分录制作年度报告（资产负债表和损益表）。

一般日记帐表（简化）包括：

  CREATE TABLE `sa_general_journal` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Date` timestamp NOT NULL DEFAULT current_timestamp(),
  `Item` varchar(1024) NOT NULL DEFAULT '',
  `Amount` decimal(9,2) NOT NULL DEFAULT 0.00,
  `Source` int(10) unsigned NOT NULL,
  `Destination` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `Date` (`Date`),
  KEY `Source` (`Source`),
  KEY `Destination` (`Destination`),
  CONSTRAINT `sa_credit-account` FOREIGN KEY (`Destination`) REFERENCES `sa_accounts` (`ID`),
  CONSTRAINT `sa_debit-account` FOREIGN KEY (`Source`) REFERENCES `sa_accounts` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=21561 DEFAULT CHARSET=utf8;

其中Amount通常（但不一定）未签名，并且是从Source帐户或类别转移到Destination类别的金额。

会计科目表（简化）包括：

CREATE TABLE `sa_accounts` (
  `ID` int(10) unsigned NOT NULL,
  `Super` int(10) unsigned,
  `Name` varchar(255) NOT NULL,
  `Type` enum('Asset','Liability','Income','Expense'),
  `Report` enum('BS','PL'), -- for "Balance Sheet" or "Profit & Loss"
  PRIMARY KEY (`ID`),
  KEY `Super` (`Super`),
  CONSTRAINT `Super` FOREIGN KEY (`Super`) REFERENCES `sa_accounts` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中ID是介于 1,000,000 和 8,999,999 之间的七位整数，未分配资金的单独条目为零。

可被 1,000,000 整除的AccountID是典型 GAAP 编号帐户方案中的“顶级”帐户：

INSERT INTO sa_account (`ID`, `Super`, `Name`, `Type`, `Report`)
  VALUES
    (0, NULL, "Not yet allocated", NULL, NULL),
    (1000000, NULL, "Assets", "Asset", "BS"),
    (2000000, NULL, "Liabilities", "Liability", "BS"),
    (3000000, NULL, "Equity", "Liability", "BS"),
    (4000000, NULL, "Income", "Income", "PL"),
    (5000000, NULL, "Expenses", "Expense", "PL"),
    (6000000, NULL, "Operating Expenses", "Expense", "PL"),
    (7000000, NULL, "Other Expenses", "Expense", "PL"),
    (8000000, NULL, "Other Income", "Income", "PL");

这些汇总帐户是抽象的，通常（但不一定）没有任何实际分配给它们。相反，子账户收到实际分配：

    INSERT INTO sa_account (`ID`, `Super`, `Name`, `Type`, `Report`)
      VALUES
        (1010000, 1000000, "Cash", "Asset", "BS"),
        (1010001, 1010000, "Cash", "Asset", "BS"),
        (1010011, 1010000, "Chequing", "Asset", "BS"),
        (1019999, 1010000, "Test bank account", "Asset", "BS"),
-- ...
        (2100000, 2000000, "Accounts Payable", "Liability", "BS"),
        (2050000, 2100000, "Lines of credit", "Liability", "BS"),
        (2052008, 2050000, "Mastercard -2008", "Liability", "BS"),
        (2054710, 2050000, "Visa -4710", "Liability", "BS"),
-- ...
        (3200000, 3000000, "Shareholder Equity", "Liability", "BS"),
        (3300000, 3000000, "Rent to own", "Liability", "BS"),
-- ...
        (4050000, 4000000, "Dairy income", "Income", "PL"),
        (4050001, 4050000, "Animals sold", "Income", "PL"),
        (4050002, 4050000, "Milk sold", "Income", "PL"),
        (4050003, 4050000, "Cheese sold", "Income", "PL"),
        (4059999, 4050000, "Test income source", "Income", "PL"),
-- ...
        (5050000, 5000000, "Dairy expense", "Expense", "PL"),
        (5050001, 5000000, "Animals bought", "Expense", "PL"),
        (5050002, 5000000, "Feed bought", "Expense", "PL"),
        (5059999, 5000000, "Test expense destination", "Expense", "PL");
-- ...

这些子帐户（通过Super）以分层关系引用其他帐户。请注意，顶级帐户在Super列中具有 NULL。

所以这里有一些测试一般日记帐分录：

INSERT INTO sa_general_journal (`ID`, `Date`, `Item`, `Amount`, `Source`, `Destination`)
  VALUES (NULL, "2020-05-03", "Test income transaction", 10.10, 4059999, 1009999),
 (NULL, "2020-05-03", "Test expense transaction", 1.01, 1009999, 5059999);

在Nick的帮助下，我能够使用 Common Table Expression 通过和accounts的差异来总结一般日记账分录SourceDestination，使用以下代码：

WITH CTE1 AS (
    SELECT
        Source AS account,
        0 AS TYPE,
        -Amount AS Amount
      FROM sa_general_journal
    UNION ALL 
    SELECT
        Destination,
        1,
        Amount
      FROM sa_general_journal gj
    )
  SELECT
      acc.ID `Account`,
      acc.Super,
      acc.Name,
      SUM(CASE WHEN CTE1.type = 0  THEN Amount END) AS Debits,
      SUM(CASE WHEN CTE1.type = 1 THEN Amount END) AS Credits,
      SUM(Amount) AS Net
    FROM CTE1
      JOIN sa_accounts acc ON CTE1.account = acc.ID
--  WHERE acc.Report = "BS"
--  WHERE acc.Report = "PL"
    GROUP BY acc.ID

到现在为止还挺好！这对我理解如何使用通用表表达式有很大帮助！

但现在，我想将子账户“汇总”到抽象账户中，得到类似于以下的预期结果：

<table>
<th>ID</th><th>Name</th><th>Debits</th><th>Credits</th><th>Net</th><th></th><th></th></tr>
<tr><td>1000000</td><td>Cash</td><td>-1.01</td><td>10.10</td><td>9.09</td><td></td><td></td></tr>
<tr><td>1009999</td><td>Cash -> Test chequing account</td><td>-1.01</td><td>10.10</td><td></td><td></td><td>9.09</td></tr>
<tr><td>4000000</td><td>Income</td><td>-10.10</td><td><i>NULL</i></td><td>-10.10</td><td></td><td></td></tr>
<tr><td>4050000</td><td>Income -> Dairy Income</td><td>-10.10</td><td><i>NULL</i></td><td></td><td>-10.10</td><td></td></tr>
<tr><td>4059999</td><td>Income -> Dairy Income -> Test income transaction</td><td>-10.10</td><td><i>NULL</i></td><td></td><td></td><td>-10.10</td></tr>
<tr><td>5000000</td><td>Expenses</td><td>-10.10</td><td><i>NULL</i></td><td>-10.10</td><td></td><td></td></tr>
<tr><td>5050000</td><td>Expenses -> Dairy Expenses</td><td>-10.10</td><td><i>NULL</i></td><td></td><td>-10.10</td><td></td></tr>
<tr><td>5059999</td><td>Expenses -> Dairy Expenses -> Test expense transaction</td><i>NULL</i></td><td>1.01</td><td></td><td></td><td>1.01</td></tr>
</table>

在一些错误的开始之后，我想出了以下简单的想法，即在上面的代码周围简单地包装一个 WITH RECURSIVE，但总结具有相同Super列的子帐户：

WITH RECURSIVE CTE2 AS
  (WITH CTE1 AS (
    SELECT
        Source AS account,
        0 AS TYPE,
        -Amount AS Amount
      FROM sa_general_journal
    UNION ALL 
    SELECT
        Destination,
        1,
        Amount
      FROM sa_general_journal gj
    )
  SELECT
      acc.ID `Account`,
      acc.Super,
      acc.Name,
      SUM(CASE WHEN CTE1.type = 0  THEN Amount END) AS Debits,
      SUM(CASE WHEN CTE1.type = 1 THEN Amount END) AS Credits,
      SUM(Amount) AS Net
    FROM CTE1
      JOIN sa_accounts acc ON CTE1.account = acc.ID
--  WHERE acc.Report = "BS"
--  WHERE acc.Report = "BS"
    GROUP BY acc.ID
  UNION ALL
  SELECT
      Name,
      SUM(CTE2.Debits),
      SUM(CTE2.Credits),
      SUM(CTE2.Net)
    FROM CTE2
    WHERE CTE2.`Super` IS NOT NULL)    
SELECT * FROM CTE2

我知道最后一个 SELECT 有问题；正如我所说，这是我的第一次尝试，但我似乎遇到了不可逾越的障碍。

执行上述代码时，我收到“查询失败。对表 'CTE2 违反了对递归定义施加的限制。错误代码 4008。” 花了相当多的搜索才发现在此类查询的递归部分中不允许聚合查询（SUM 等）。叹。

我读过 WITH RECURSIVE，SQL 变得与图灵兼容，所以它必须可以做我正在寻找的东西，但是在递归查询中没有 SUM()，很难想象如何解决这个问题！

标签： mysqlcommon-table-expressionfinancerecursive-datastructuresaccounting

解决方案

此查询应为您提供所需的结果。它基于您上一个问题的答案，并添加了一个递归 CTE，它将每笔交易复制到层次结构中它上面的所有帐户。然后在最终查询中汇总每个帐户的值：

WITH RECURSIVE xfers AS (
  SELECT Source AS account,
         0 AS TYPE,
         -Amount AS Amount
  FROM sa_general_journal
  UNION ALL 
  SELECT Destination,
         1,
         Amount
  FROM sa_general_journal gj
),
dbcr AS ( 
  SELECT
      acc.ID `Account`,
      acc.Super,
      acc.Name,
      COALESCE(SUM(CASE WHEN x.type = 0  THEN Amount END), 0) AS Debits,
      COALESCE(SUM(CASE WHEN x.type = 1 THEN Amount END), 0) AS Credits,
      COALESCE(SUM(Amount), 0) AS Net
  FROM sa_accounts acc
  LEFT JOIN xfers x ON x.account = acc.ID
--  WHERE acc.Report = "BS"
--  WHERE acc.Report = "PL"
  GROUP BY acc.ID
),
summary AS (
  SELECT * 
  FROM dbcr
  WHERE Net != 0
  UNION ALL
  SELECT d.Account, d.Super, d.Name, s.Debits, s.Credits, s.Net
  FROM dbcr d
  JOIN summary s ON d.Account = s.Super
  WHERE s.Super IS NOT NULL
)
SELECT Account, Super, Name, 
       SUM(Debits) AS Debits,
       SUM(Credits) AS Credits,
       SUM(Net) AS Net
FROM summary
GROUP BY Account, Super, Name
ORDER BY Account

输出（用于我的扩展演示）：

Account     Super       Name                Debits  Credits     Net
1000000     null        Assets              -6.31   10.1    3.79
1010000     1000000     Cash                -6.31   10.1    3.79
1010011     1010000     Chequing            -5.3    0       -5.3
1019999     1010000     Test bank account   -1.01   10.1    9.09
4000000     null        Income              -10.1   0       -10.1
4050000     4000000     Dairy income        -10.1   0       -10.1
4059999     4050000     Test income source  -10.1   0       -10.1
5000000     null        Expenses            0       6.31    6.31
5050002     5000000     Feed bought         0       5.3     5.3
5059999     5000000     Test expense dest   0       1.01    1.01

dbfiddle 上的演示

推荐阅读