一个关于统计的问题，急，谢谢

Question

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.

Answer 1

select a.类别 ,lpad(' ',a.层数*2,' ')||a.名称 name,(select sum(数量) from tab2 b where instr(b.代码,a.类别)>0)  数目 from tab1 a order by substr(a.类别,1,1),length(a.类别)

Answer 2

select 类别，名称，数量
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 类别
；
这样就可以了