SQL> select * from item;ITEM_ID BILLS_LEVEL ITEM_NAME
---------- ----------- ---------------
1-1 2 AAA
1-1-1 3 AAA_1
1-1-2 3 AAA_2
2-1 2 BBB
2-1-1 3 BBB_1
2-1-2 3 BBB_2
2-1-3 3 BBB_3
3-1 2 CCC
3-1-1 3 CCC_19 rows selected.SQL> select * from item_stock;ITEM_ID QTY
---------- ----------
1-1-1 3
1-1-2 2
2-1-1 10
2-1-2 3
2-1-3 5
3-1-1 6
3-1-1 67 rows selected.SQL> select * from (
2 select a.item_id id,lpad(' ',a.bills_level*2,' ')||a.item_name name,sum(b.qty)
3 from item a,item_stock b
4 where b.item_id like a.item_id||'%'
5 group by a.item_id,a.item_name,a.bills_level)
6 order by id;ID NAME SUM(B.QTY)
---------- -------------------- ----------
1-1 AAA 5
1-1-1 AAA_1 3
1-1-2 AAA_2 2
2-1 BBB 18
2-1-1 BBB_1 10
2-1-2 BBB_2 3
2-1-3 BBB_3 5
3-1 CCC 12
3-1-1 CCC_1 129 rows selected.
---------- ----------- ---------------
1-1 2 AAA
1-1-1 3 AAA_1
1-1-2 3 AAA_2
2-1 2 BBB
2-1-1 3 BBB_1
2-1-2 3 BBB_2
2-1-3 3 BBB_3
3-1 2 CCC
3-1-1 3 CCC_19 rows selected.SQL> select * from item_stock;ITEM_ID QTY
---------- ----------
1-1-1 3
1-1-2 2
2-1-1 10
2-1-2 3
2-1-3 5
3-1-1 6
3-1-1 67 rows selected.SQL> select * from (
2 select a.item_id id,lpad(' ',a.bills_level*2,' ')||a.item_name name,sum(b.qty)
3 from item a,item_stock b
4 where b.item_id like a.item_id||'%'
5 group by a.item_id,a.item_name,a.bills_level)
6 order by id;ID NAME SUM(B.QTY)
---------- -------------------- ----------
1-1 AAA 5
1-1-1 AAA_1 3
1-1-2 AAA_2 2
2-1 BBB 18
2-1-1 BBB_1 10
2-1-2 BBB_2 3
2-1-3 BBB_3 5
3-1 CCC 12
3-1-1 CCC_1 129 rows selected.
from
(
(select t1.类别 as 类别,t1.名称 名称,sum(t2.数量) 数量 as
from table1 t1,table2 t2
where substr(t2.类别,1,3) = t1.类别 and t1.层次 = 2
group by t1.类别,t1.名称)
union
(select t1.类别 as 类别,t1.名称 as 名称,t2.数量 as 数量
from table2 t2,table1 t1
where t1.类别 = t2.类别)
)
order by 类别
;
这样就可以了