需要基于另一列对一列取反，作为总计的一部分

本文介绍了需要基于另一列对一列取反，作为总计的一部分的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在产生Profit&会计系统中的损失报告.

I'm having trouble generating Profit & Loss reports in an accounting system.

每个常规日记帐分录都有一个金额，一个源帐户和一个目标帐户.这是一个简化的架构，以及一些示例数据:

Each general journal entry has an amount, a source account, and a destination account. Here is a simplified schema, and some sample data:

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;

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


INSERT INTO sa_account (`ID`, `Name`, `Type`, `Report`)
  VALUES (1009999, "Test chequing account", "Asset", "BS"),
         (4059999, "Test Income account", "Income", "PL"),
         (5059999, "Test Expense account", "Expense", "PL");
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);

这是收入$ 10.10，存入支票帐户，而支出$ 1.01来自支票帐户.

This is $10.10 of income, deposited in the chequing account, and an expense of $1.01 that comes out of the chequing account.

要获得利润和利润的余额，亏损声明，需要对每次在 Source 列中出现的每个帐户的所有 Amount 项求和，然后减去所有 Amount 项该帐户在 Destination 列中的位置.

To obtain a balance for a Profit & Loss statement, one needs to sum all the Amount entries for each occurrence of an account in the Source column, and then subtract all the Amount entries where that account is in the Destination column.

预期结果将是:

<table>
<th>ID</th><th>Name</th><th>Debits</th><th>Credits</th><th>Net</th></tr>
<tr><td>1009999</td><td>Test chequing account</td><td>-1.01</td><td>10.10</td><td>9.09</td></tr>
<tr><td>4059999</td><td>Test income transaction</td><td>-10.10</td><td><i>NULL</i></td><td>-10.10</td></tr>
<tr><td>5059999</td><td>Test expense transaction</td><td><i>NULL</i></td><td>1.01</td><td>1.01</td></tr>
</table>

我的第一种方法是幼稚的，要查询与 sa_accounts 表结合的 sa_general_journal 表，该表由 Source 和 Destination 列，如果 Destination 包含感兴趣的帐户，则使用IF函数取反 Amount .使用预先准备好的语句查询单个帐户时，我可以成功完成此操作:

My first approach was somewhat naive, to query the sa_general_journal table joined with the sa_accounts table, selected by either the Source and Destination columns, using an IF function to negate the Amount if the Destination contained the account of interest. I do this successfully when querying an individual account using a prepared statement:

SELECT
  DATE_FORMAT(exp.Date, '%Y') AS `Year`,
  AVG(exp.Amount) AS `Avg`,
  SUM(IF(Source = ?, 0 - exp.Amount, NULL)) AS `Debits`,
  SUM(IF(Destination = ?, exp.Amount, NULL)) AS `Credits`,
  SUM(IF(Source = ?, 0 - exp.Amount, exp.Amount)) AS `Net`
FROM sa_general_journal exp
  LEFT JOIN sa_accounts Destination ON exp.Destination = Destination.ID
WHERE Destination = ?
  OR Source = ?
GROUP BY `Year`

所有四个占位符都具有相同的帐户ID.

where all four placeholders hold the same account ID.

但是，当按不带WHERE子句的帐户ID分组以获取所有帐户余额的列表时，此操作将失败—在占位符中用动态帐户ID代替静态帐户ID似乎永远不会达到"0-exp.数量"代码…Du！该查询被选择为一个集合，而不是过程中的步骤.我明白了.

But this fails when GROUPing by account ID without the WHERE clause, to get a listing of all account balances — substituting a dynamic account ID in place of the static account ID in a placeholder never appears to hit the "0 - exp.Amount" code… Duh! The query is selected as a set, not steps in a procedure. I get that.

使SUM语句子查询有效，但速度非常慢，显然对成千上万条记录中的每一个都进行三个子查询！

Making the SUM statements subqueries works, but it is dreadfully slow, apparently doing the three subqueries for each of tens of thousands of records!

所以，我认为我可以使用几个Common Table Expressions来分解子查询，但这似乎也无法正常工作，因为它似乎仍然只是返回SUM( Amount )，而不减去 Destination 的 Amount ，而不是 Source s.

So, I thought I could factor the subqueries with a couple Common Table Expressions, but this does not appear to work properly, either, as it is still seems to be simply returning SUM(Amount), without subtracting the Amounts that are Destinations rather than Sources.

WITH
 source1 AS (SELECT
  src.ID,
  src.Name,
  SUM(Amount) Amount
FROM sa_general_journal gj
  LEFT JOIN sa_accounts src ON gj.`Source` = src.ID
WHERE src.Report = "PL" -- AND YEAR(`Date`) = 2019
GROUP BY src.ID),
 destination1 AS (SELECT
  dst.ID,
  dst.Name,
  SUM(0-Amount) Amount
FROM sa_general_journal gj
  LEFT JOIN sa_accounts dst ON gj.`Destination` = dst.ID
WHERE dst.Report = "PL" --  AND YEAR(`Date`) = 2019
GROUP BY dst.ID)
SELECT ID, Name, sum(Amount)
FROM source1
UNION ALL
SELECT ID, Name, sum(Amount)
FROM destination1
GROUP BY ID

我猜我在做一些愚蠢的假设，或者做一些愚蠢的事情，所以任何建议都值得赞赏！

I'm guessing I'm making some silly assumption, or doing something stupid, so any advice is appreciated!

作为总计的一部分

需要基于另一列对一列取反，作为总计的一部分

问题描述

推荐答案