我有一个表Account，在这个结构
中，Account_id作为唯一的PK
Value Name Account_id
------ ------- ----------
1 Assets 100
11 Fixed Assets 101
111 Furniture and office equipment 102
1111 Chairs 105
1112 Computers 104
1113 Air Conditioners 103
12 Under-Development Projects 108
121 Lands 109
122 Roads and Buildings 110
1221 Buildings 111
2 Revenues 107
21 Hotel 120
211 Room 122
212 Sweet 123
22 House 125
3 Liabilities 113
and so on...
值为 11 和 12(固定资产和在建项目(的科目是值为 1(资产(的子账户。 账户 111 也是 11 的子账户，账户 1111 和 1112 和1113 是111的子账户。 为了更好地理解，我们可以说级别 1、级别 2、级别 3 和级别 4。 喜欢这
level1 level2 level3 level4
1 11 111 1111
1112
1113
12 121
122 1221
2 21 211
212
22
希望你能明白这个想法。
现在，在此结构中，我有另一个表Payment来处理付款
Account_id Debit Credit
------------ ------- --------
105 500000 0
103 350000 0
110 0 300000
105 0 300000
111 250000 0
105 100000 0
111 0 400000
122 400000 0
123 0 250000
注意：付款仅在 3 级和 4 级帐户
上，生成的表应如下所示
Value Name Amount
------ ----- ---------
1 Assets 200,000
11 Fixed Assets 650,000
111 Furniture and office equipment 650,000
1111 Chairs 300,000
1112 Computers 0
1113 Air Conditioners 350,000
12 Under-Development Projects -450,000
121 Lands 0
122 Roads and Buildings -450,000
1221 Buildings -150,000
2 Revenue 150,000
21 Hotel 150,000
211 Room 400,000
212 Sweet -150,000
and so on...
"金额"列是Debit-Credit的结果。以下是每个帐户的简要说明，
每个帐户将显示其所有子帐户的全部金额。 例如，家具和办公设备(值111(，将是：amount of 111 + amount of 1111 + amount of 1112 + amount of 1113
即 500000+350000-300000+100000=650000
将付款左加入帐户，总金额。使用这些行创建递归 CTE。起点是子项，其 id 不是其他子字符串。然后递归附加父项：
with t(value, name, account_id, amount) as (
select value, name, account_id, sum(debit - credit)
from account a
natural left join payment p
group by account_id, value, name),
c(value, name, account_id, amount) as (
select value, name, account_id, amount
from t
where not exists (select 1 from account where value like t.value||'_' )
union all
select t.value, t.name, t.account_id, nvl(c.amount, 0) + nvl(t.amount, 0)
from t join c on t.value = substr(c.value, 1, length(c.value) - 1))
select value, name, account_id, sum(amount) amount
from c
group by value, name, account_id
order by to_char(value)
DBfiddle 演示
使用与account.value列的前导子字符串相关的子查询：
SELECT a.*,
COALESCE(
( SELECT SUM( debit - credit )
FROM payment p
INNER JOIN account ac
ON ( p.account_id = ac.account_id )
WHERE ac.value LIKE a.value || '%'
),
0
) AS amount
FROM account a
其中输出：
价值 |姓名 |ACCOUNT_ID | 量 :---- |:----------------------------- |---------: |------: 1 |资产 | 100 | 200000 11 |固定资产 | 101 | 650000 111 |家具和办公设备 | 102 | 650000 1111 |椅子 | 105 | 300000 1112 |计算机 | 104 | 0 1113 |空调 | 103 | 350000 12 |开发中项目 | 108 |-450000 121 |土地 | 109 | 0 122 |道路和建筑物 | 110 |-450000 1221 |建筑 | 111 |-150000 2 |收入 | 107 | 150000 21 |酒店 | 120 | 150000 211 |客房 | 122 | 400000 212 |甜蜜 | 123 |-250000 22 |房子 | 125 | 0 3 |负债 | 113 | 0
db<>小提琴在这里