自表关联查询
实例：

C1 C2 C3 C4 C5 c6
记录1 总金额1 10000元 来源额度ID：null 去向额度ID：1 总用途1

记录2 金额1 500元 来源额度ID:1 去向额度ID:2 用途1

记录3 金额2 1000元 来源额度ID:1 去向额度ID:3 用途2

记录4 金额3 2000元 来源额度ID:1 去向额度ID：4 用途3

记录5 总金额2 5000元 来源额度ID:null 去向额度ID：5 总用途2

记录6 金额4 100元 来源额度ID:5 去向额度ID:6 用途4

记录7 金额5 100元 来源额度ID:5 去向额度ID：7 用途5

...

要求统计总金额1，总金额2，总金额..的汇总金额 以及每条总金额分出来的分项金额的汇总金额 以及每条总金额剩下的余额，如例子中总金额1 10000元 用掉3500元，还剩6500元 总金额2 5000元 用掉200元，还剩4800元 一共总金额有15000元 用掉3700元 还剩11300元 并且只关心各项总金额的总用途，用其中的来源去向额度ID进行总金额及分项金额的关联

------解决方案--------------------
select a.c2, a.c3, b.cost, a.c3 - b.cost, c6 
from (select * from table_name where c4 is null) a, 
( select c4, count(c3) cost from table_name group by c4) b
where a.c1 = b.c4
------解决方案--------------------
试试这个吧，要想把所有的分类都一句话写出来太复杂了，我只写了部分分类，没有完全达到按小类相减。

--C1 C2 C3 C4 C5 c6
WITH t AS
(SELECT '总金额1' AS code,
10000 AS amount,
0 AS res,
1 AS dist,
'总用途1' AS costType
FROM dual
UNION ALL
SELECT '金额1',500,1,2,'用途1' FROM dual
UNION ALL
SELECT '金额2', 1000, 1 ,3, '用途2' FROM dual
UNION ALL
SELECT '金额3', 2000, 1 ,4, '用途3' FROM dual
UNION ALL
SELECT '总金额2', 5000, 0, 5 ,'总用途2' FROM dual
UNION ALL
SELECT '金额4', 100, 5 ,6, '用途4' FROM dual
UNION ALL
SELECT '金额5', 100, 5 ,7, '用途5' FROM dual
)
SELECT
CASE
WHEN res1=0 AND dist1=1 THEN '小计' || decode(res,0,'收入：','支出：')
WHEN res1=1
AND dist1=1
THEN '总计：'
ELSE ''
END AS descript ,
amount
FROM
(SELECT grouping(res) AS res1,
grouping(dist) AS dist1,
res,
dist,
SUM(amount) AS amount
FROM
(SELECT res,
dist,
code,
SUM(DECODE(res,0,amount,-amount))over(partition BY dist order by res) AS amount
FROM
( SELECT * FROM t START WITH res = 0 CONNECT BY res = prior dist
)
)
GROUP BY rollup(res,dist)
) a

--结果
DESCRIPT AMOUNT
-------- ---------------------- 
10000
5000
小计收入： 15000
-500
-1000
-2000
小计支出： -3500
-100
-100
小计支出： -200
总计： 11300

 11 rows selected
------解决方案--------------------
--多加一层自联接，可以准确显示收入与支出说明
--C1 C2 C3 C4 C5 c6
WITH t AS
(SELECT '总金额1' AS code,
10000 AS amount,
0 AS res,
1 AS dist,
'总用途1' AS costType
FROM dual
UNION ALL
SELECT '金额1',500,1,2,'用途1' FROM dual
UNION ALL
SELECT '金额2', 1000, 1 ,3, '用途2' FROM dual
UNION ALL
SELECT '金额3', 2000, 1 ,4, '用途3' FROM dual
UNION ALL
SELECT '总金额2', 5000, 0, 5 ,'总用途2' FROM dual
UNION ALL
SELECT '金额4', 100, 5 ,6, '用途4' FROM dual
UNION ALL
SELECT '金额5', 100, 5 ,7, '用途5' FROM dual
)
SELECT
CASE
WHEN res1=0 AND dist1=1 THEN decode(b.code,null,'收入','支出（' || b.code) || '）小计:'
WHEN res1=1
AND dist1=1
THEN '总计：'
ELSE ''
END AS descript ,
a.amount
FROM
(SELECT grouping(res) AS res1,
grouping(dist) AS dist1,
res,
dist,
SUM(amount) AS amount
FROM
(SELECT res,
dist,
code,
SUM(DECODE(res,0,amount,-amount))over(partition BY dist order by res) AS a