计算资产负债表

Question

使用以下查询：

select b1.Name DrBook, c1.Name DrControl, b2.Name CrBook, c2.Name CrControl, tn.Amount
from Transactions tn
left join Books b1 on b1.Id = tn.DrBook
left join Books b2 on b2.Id = tn.CrBook
left join ControlLedgers c1 on c1.Id = tn.DrControl
left join ControlLedgers c2 on c2.Id = tn.CrControl

我得到了资产负债表的结果集：

+---------------------+-----------+---------------------+-----------+--------+
|       DrBook        | DrControl |       CrBook        | CrControl | Amount |
+---------------------+-----------+---------------------+-----------+--------+
| Current Assets      | Cash      | Fund                | Initial   | 100000 |
| Current Assets      | Cash      | Fund                | Initial   | 100000 |
| Current Assets      | Cash      | Fund                | Initial   | 100000 |
| Current Assets      | Cash      | Fund                | Initial   | 100000 |
| Current Assets      | Cash      | Fund                | Initial   | 100000 |
| Expenses            | Foods     | Current Liabilities | Payables  |  10000 |
| Current Liabilities | Payables  | Current Assets      | Cash      |   5000 |
+---------------------+-----------+---------------------+-----------+--------+

要在我的应用程序中显示资产负债表，我现在要做的是发出两个查询并获得两个结果集，如下所示：

query1:

select b1.Name DrBook, c1.Name DrControl, SUM(tn.Amount) Amount
from Transactions tn
left join Books b1 on b1.Id = tn.DrBook
left join ControlLedgers c1 on c1.Id = tn.DrControl
group by DrBook, DrControl

结果集1:

+---------------------+-----------+--------+
|       DrBook        | DrControl | Amount |
+---------------------+-----------+--------+
| Current Assets      | Cash      | 500000 |
| Expenses            | Foods     |  10000 |
| Current Liabilities | Payables  |   5000 |
+---------------------+-----------+--------+

查询2:

select b1.Name CrBook, c1.Name CrControl, SUM(tn.Amount) Amount
from Transactions tn
left join Books b1 on b1.Id = tn.CrBook
left join ControlLedgers c1 on c1.Id = tn.CrControl
group by CrBook, CrControl

结果集2:

+---------------------+-----------+--------+
|       CrBook        | CrControl | Amount |
+---------------------+-----------+--------+
| Current Assets      | Cash      |   5000 |
| Current Liabilities | Payables  |  10000 |
| Fund                | Initial   | 500000 |
+---------------------+-----------+--------+

如果是资产或支出(在这种情况下是流动资产和支出)，则从结果集1减去结果集2，如果是负债、收入或基金(在这种情况下是流动负债和基金)，则从结果集2减去结果集1，以获得如下最终结果集：

+---------------------+---------------+---------+
|        Book         | ControlLedger | Balance |
+---------------------+---------------+---------+
| Current Assets      | Cash          |  495000 |
| Expenses            | Food          |   10000 |
| Current Liabilities | Payables      |    5000 |
| Fund                | Initial       |  500000 |
+---------------------+---------------+---------+

我尝试了一些case语句，通过sql查询获得最终结果集，而不是在应用程序代码中手动计算，但这些语句都没有工作！

编辑

以下是表的定义：

CREATE TABLE "Transactions"(
    "Id"            INTEGER NOT NULL,
    "Date"          TEXT NOT NULL,
    "DrBook"        INTEGER NOT NULL,
    "CrBook"        INTEGER NOT NULL,
    "DrControl"     INTEGER NOT NULL,
    "CrControl"     INTEGER NOT NULL,
    "DrLedger"      INTEGER,
    "CrLedger"      INTEGER,    
    "DrSubLedger"   INTEGER,
    "CrSubLedger"   INTEGER,
    "DrPartyGroup"  INTEGER,
    "CrPartyGroup"  INTEGER,
    "DrParty"       INTEGER,
    "CrParty"       INTEGER,
    "DrMember"      INTEGER,
    "CrMember"      INTEGER,
    "Amount"        INTEGER NOT NULL,
    "Narration"     TEXT,

    FOREIGN KEY("DrBook") REFERENCES "Books"("Id"),
    FOREIGN KEY("CrBook") REFERENCES "Books"("Id"),
    FOREIGN KEY("DrControl") REFERENCES "ControlLedgers"("Id"),
    FOREIGN KEY("CrControl") REFERENCES "ControlLedgers"("Id"),
    FOREIGN KEY("DrLedger") REFERENCES "Ledgers"("Id"),
    FOREIGN KEY("CrLedger") REFERENCES "Ledgers"("Id"),
    FOREIGN KEY("DrSubLedger") REFERENCES "SubLedgers"("Id"),
    FOREIGN KEY("CrSubLedger") REFERENCES "SubLedgers"("Id"),
    FOREIGN KEY("DrPartyGroup") REFERENCES PartyGroups("Id"),
    FOREIGN KEY("CrPartyGroup") REFERENCES PartyGroups("Id"),
    FOREIGN KEY("DrParty") REFERENCES "Parties"("Id"),
    FOREIGN KEY("CrParty") REFERENCES "Parties"("Id"),
    FOREIGN KEY("DrMember") REFERENCES "Members"("Id"),
    FOREIGN KEY("CrMember") REFERENCES "Members"("Id")  
);

对于每一日刊，我插入一行，它同时包含借方、贷方和金额信息。我没有Dr/CrProduct或Dr/CrServices，因为这是为个人和家庭的簿记和会计而设计的。

例如，从A先生那里购买食物，我通过(1)：

Expenses -> Food -> Rice -> Fine Rice A/c          Dr. 10000
    Current Liabilities -> Payables A/C            Cr. 10000

如果它是赊销的，当购买金额以现金支付时，我同意(2)：

Current Liabilities -> Payables A/C                 Dr. 10000
    Current Assets -> Cash -> In Hand -> Emon A/c   Cr. 10000

表中的内容如下：

+----+------------+--------+--------+-----------+-----------+----------+----------+-------------+-------------+--------------+--------------+---------+---------+----------+----------+--------+----------------+
| Id |    Date    | DrBook | CrBook | DrControl | CrControl | DrLedger | CrLedger | DrSubLedger | CrSubLedger | DrPartyGroup | CrPartyGroup | DrParty | CrParty | DrMember | CrMember | Amount |   Narration    |
+----+------------+--------+--------+-----------+-----------+----------+----------+-------------+-------------+--------------+--------------+---------+---------+----------+----------+--------+----------------+
|  3 | 2020-06-15 |      3 |      5 |         9 |        18 |        2 |          |           2 |             |              |            4 |         |       1 |          |          |  10000 | Some Narration |
|  3 | 2020-06-15 |      5 |      2 |        18 |         7 |          |        1 |             |           1 |            4 |              |       1 |         |          |          |  10000 |                |
+----+------------+--------+--------+-----------+-----------+----------+----------+-------------+-------------+--------------+--------------+---------+---------+----------+----------+--------+----------------+

下面是第一排的快速剖析：

+--------------+----------------+---------------------+
|   Columns    |     Values     |      Mappings       |
+--------------+----------------+---------------------+
| DrBook       | 3              | Expenses            |
| CrBook       | 5              | Current Liabilities |
| DrControl    | 9              | Food                |
| CrControl    | 18             | Payables            |
| DrLedger     | 2              | Rice                |
| DrSubLedger  | 2              | Fine Rice           |
| CrPartyGroup | 4              | Groceries           |
| CrParty      | 1              | Mr. A               |
| Amount       | 10000          |                     |
| Narration    | Some Narration |                     |
+--------------+----------------+---------------------+

Answer 1

不确定以下是否是唯一的办法：

with t1(Id, Book, Control, Amount) as (
    select tn.DrBook, b1.Name, c1.Name, sum(tn.Amount)
    from Transactions tn
    left join Books b1 on b1.Id = tn.DrBook
    left join ControlLedgers c1 on c1.Id = tn.DrControl
    group by DrBook, DrControl
), 
t2 as (
    select tn.CrBook, b1.Name, c1.Name, -1*sum(tn.Amount)
    from Transactions tn
    left join Books b1 on b1.Id = tn.CrBook
    left join ControlLedgers c1 on c1.Id = tn.CrControl
    group by CrBook, CrControl
), 
t3 as (
    select * from t1 union all select * from t2
)
select Book, Control,
    case when Id <=3 then sum(Amount)
    else -1*sum(Amount) end Balance
from t3 group by Book, Control order by Id

它产生了我所期望的结果：

+---------------------+----------+---------+
|        Book         | Control  | Balance |
+---------------------+----------+---------+
| Current Assets      | Cash     |  495000 |
| Current Liabilities | Payables |    5000 |
| Expenses            | Foods    |   10000 |
| Fund                | Initial  |  500000 |
+---------------------+----------+---------+