mysql - 从只有一个金额列和借方贷方列的表中选择用于试算表显示的语句

Question

我正在尝试使用来自 MySQL/MariaDB 的 SQL 查询来填充数据表，以将其显示为报告的试算表。

我的数据库引擎是 MariaDB 10.4.19

对于这个例子..我有 2 个表。

1. 日志(包含交易)和

| id | date       | debit | credit| amount    |
|----|------------|-------|-------|-----------|
| 1  | 2021-09-01 | 8     | 2     | 5000.000  |
| 6  | 2021-09-22 | 22    | 17    | 4750.000  |
| 8  | 2021-09-05 | 8     | 3     | 1485.000  |
| 9  | 2021-08-10 | 8     | 6     | 108.000   |
| 10 | 2021-07-07 | 8     | 23    | 98756.000 |

1. 分类帐帐户名称

| id | name                              | desc                              | status |
|----|-----------------------------------|-----------------------------------|--------|
| 1  | Assets                            | Current Assets                    | 1      |
| 2  | Stockholders equity               | Stockholders or Owners equity     | 1      |
| 3  | Liability                         | Liabilities related accounts      | 1      |
| 4  | Operating Revenues                | Operating Revenues                | 1      |
| 5  | Operating Expenses                | Operating Expenses                | 1      |
| 6  | Non-operating revenues and gains  | Non-operating revenues and gains  | 1      |
| 7  | Non-operating expenses and losses | Non-operating expenses and losses | 1      |
| 8  | Cash                              | For cash transaction              | 1      |

现在在我的程序中，如果我选择一个帐户(从分类帐中填充)并提交，它将发送它的 ID。使用该帐户 ID，我已根据该帐户 ID 填充了所有交易。

例如:如果我选择现金账户，它会发送让我们说它会发送 8 作为 id。

现在

1. SQL 查询必须从日记帐中填充日期和金额，以及从分类帐中填充帐户名称 - 交易中 ID 为 8 的帐户名称。例如，如果它是 2，那么它将返回“股东权益”作为名称。
2. 如果给定的 id 8 在借方，金额应该在 credit 列，如果给定的 id 8 在贷方，金额应该在 debit 列

如上几点，从程序中，如果帐户 ID 作为 8 传递(即“现金”)然后SQL SELECT 查询结果的DESIRED OUPUT 应如下所示...

| Date       | Account                          |    Debit |    Credit |
|------------|----------------------------------|---------:|----------:|
| 2021-09-01 | Stockholders equity              |     0.00 |   5000.00 |
| 2021-09-05 | Liability                        |     0.00 |   1485.00 |
| 2021-08-10 | Non-operating revenues and gains |     0.00 |    108.00 |
| 2021-07-07 | Land                             |     0.00 |  98756.00 |
| 2021-02-25 | Land                             | 21564.00 |      0.00 |
| 2021-01-19 | Vehicles                         |     0.00 |  23132.00 |
| 2020-05-19 | Buildings Asset                  |   465.00 |      0.00 |
| 2019-09-01 | Non-operating revenues and gains |   315.00 |      0.00 |
| 2019-05-23 | Land                             |   346.00 |      0.00 |
| 2018-04-12 | Cash                             |     0.00 |    697.00 |
| 2017-05-15 | Non-operating revenues and gains |     0.00 |    999.00 |
| 2018-06-18 | Operating Revenues               |     0.00 |    496.00 |
| 2018-06-23 | Liability                        |     0.00 |    426.00 |
| 2019-12-12 | Assets                           |     0.00 |   1684.00 |
| 2018-07-15 | Land                             |     0.00 |   1649.00 |
| 2018-07-22 | Land                             |  3666.00 |      0.00 |
| 2018-05-14 | Non-operating revenues and gains |     0.00 |    489.00 |
| 2018-09-16 | Equipment                        |   692.00 |      0.00 |
| 2021-04-18 | Non-operating revenues and gains |  4986.00 |      0.00 |
| 2020-04-19 | Land                             |  4956.00 |      0.00 |
| 2019-03-15 | Buildings Asset                  |     0.00 |   4988.00 |
| 2019-12-04 | Inventory                        |     0.00 |   7946.00 |
| 2019-08-25 | Stockholders equity              |     0.00 |  19449.00 |
|            |                                  |          |           |
|            | Total                            | 36990.00 | 167304.00 |
|            | Balance                          |          | 130314.00 |

这是 SQLFiddle 中的示例数据 http://sqlfiddle.com/#!9/fe2a00/2

能否请您告诉我如何使用连接和/或联合使用 SQL 查询填充此结果表，或者我应该使用其他方式...

请帮忙解答!

Answer 1

没有总计:

SELECT journal.date `Date`,
       ledger.name Account,
       CASE WHEN journal.dracc = 8 
            THEN 0.000
            ELSE amount
            END Debit,
       CASE WHEN journal.dracc = 8
            THEN amount
            ELSE 0.000
            END Credit
FROM journal
JOIN ledger ON (ledger.id, 8) IN ((dracc, cracc), (cracc, dracc));

总计:

SELECT CASE WHEN NOT GROUPING(journal.id) 
            THEN MAX(journal.date) 
            END `Date`,
       CASE WHEN NOT GROUPING(journal.id) 
            THEN MAX(ledger.name) 
            ELSE 'Total'
            END Account,
       SUM(CASE WHEN journal.dracc = 8 
                THEN 0
                ELSE amount
                END) Debit,
       SUM(CASE WHEN journal.dracc = 8
                THEN amount
                ELSE 0
                END) Credit
FROM journal
JOIN ledger ON (ledger.id, 8) IN ((dracc, cracc), (cracc, dracc))
GROUP BY journal.id WITH ROLLUP;

Total 和 Balance:

WITH 
cte AS (
SELECT CASE WHEN NOT GROUPING(journal.id) 
            THEN MAX(journal.date) 
            END `Date`,
       CASE WHEN NOT GROUPING(journal.id) 
            THEN MAX(ledger.name) 
            ELSE 'Total'
            END Account,
       SUM(CASE WHEN journal.dracc = 8 
                THEN 0
                ELSE amount
                END) Debit,
       SUM(CASE WHEN journal.dracc = 8
                THEN amount
                ELSE 0
                END) Credit
FROM journal
JOIN ledger ON (ledger.id, 8) IN ((dracc, cracc), (cracc, dracc))
GROUP BY journal.id WITH ROLLUP
)
SELECT * 
FROM cte 
UNION ALL
SELECT NULL, 'Balance', NULL, Credit - Debit
FROM cte
WHERE `Date` IS NULL;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=794f87b054848b1584a6c8dd2dd5d47c

如果您的 MySQL 版本是 5.x，则:

• 将 WHEN NOT GROUPING(journal.id) 替换为 WHEN journal.id IS NOT NULL
• 将 CTE 转换为子查询