myTable 테이블 데이타가 useTable 테이블 안에 100% 다 있는데 굳이 조인을 해야 할까용?

with usetable as
( select 1 no, '1-1' subject, 0 re_no, 1 order_num from dual union all
 select 2, '학용품', 1, 2 from dual union all
 select 3, '과자', 1, 3 from dual union all
 select 4, '2-1', 0, 1 from dual union all
 select 5, '소풍', 4, 2 from dual union all
 select 6, '병원', 1, 4 from dual union all
 select 7, '자전거', 4, 3 from dual
), detailtable as
( select 2 no, 10 preamt, 100 useamt, 300 jusamt from dual union all
 select 3, 50, 20, 30 from dual union all
 select 5, 200, 150, 200 from dual union all
 select 6, 60, 70, 20 from dual union all
 select 7, 80, 300, 50 from dual
)
select no
   , subject
   , preamt
   , useamt
   , jusamt
   , useamt - jusamt "증감"
from (select u.no no
      , subject
      , nvl(preamt, sum(preamt) over(partition by rno)) preamt
      , nvl(useamt, sum(useamt) over(partition by rno)) useamt
      , nvl(jusamt, sum(jusamt) over(partition by rno)) jusamt
   from (select no, subject, re_no, connect_by_root no as rno
      from usetable
      start with re_no = 0
      connect by prior no = re_no) u
     , detailtable d
   where u.no = d.no(+)
   order by rno, u.no)
;   

WITH USETABLE(no, subject, re_no, order_num) AS (
	SELECT '1','1-1','0','1' FROM DUAL UNION ALL
	SELECT '2','학용품','1','2' FROM DUAL UNION ALL
	SELECT '3','과자','1','2' FROM DUAL UNION ALL
	SELECT '4','2-1','0','1' FROM DUAL UNION ALL
	SELECT '5','소풍','4','2' FROM DUAL UNION ALL
	SELECT '6','병원','1','4' FROM DUAL UNION ALL
	SELECT '7','자전거','4','3' FROM DUAL
--), MYTABLE(no, re_no, order_num) AS (
--	SELECT '1','0','1' FROM DUAL UNION ALL
--	SELECT '2','1','2' FROM DUAL UNION ALL
--	SELECT '3','1','3' FROM DUAL UNION ALL
--	SELECT '4','0','1' FROM DUAL UNION ALL
--	SELECT '5','4','2' FROM DUAL UNION ALL
--	SELECT '6','1','4' FROM DUAL UNION ALL
--	SELECT '7','4','3' FROM DUAL 
), DETAILTABLE(no, preAmt, useAmt, jusAmt) AS (
	SELECT '2','10','100','300' FROM DUAL UNION ALL
	SELECT '3','50','20','30' FROM DUAL UNION ALL
	SELECT '5','200','150','200' FROM DUAL UNION ALL
	SELECT '6','60','70','20' FROM DUAL UNION ALL
	SELECT '7','80','300','50' FROM DUAL
)
SELECT
  rn, subject, preamt, useamt, jusamt,
  useamt - jusamt AS 증감
FROM (
  SELECT
    rn, subject, 
    DECODE(preamt, NULL,
      SUM(preamt) OVER(PARTITION BY DECODE(re_no, 0, MT.no, re_no)),
      preamt
    ) AS preamt,
    DECODE(useamt, NULL,
      SUM(useamt) OVER(PARTITION BY DECODE(re_no, 0, MT.no, re_no)),
      useamt
    ) AS useamt,
    DECODE(jusamt, NULL,
      SUM(jusamt) OVER(PARTITION BY DECODE(re_no, 0, MT.no, re_no)),
      jusamt
    ) AS jusamt
  FROM
    (SELECT LEVEL AS lv, ROWNUM AS rn, USETABLE.* FROM USETABLE START WITH re_no = 0 CONNECT BY PRIOR no = re_no) MT,
    DETAILTABLE DT
  WHERE
    MT.no = DT.no(+)
)
ORDER BY
  rn
;

음... 갱신 했을 때 답글이 없었는데... 뉴ㅅ뉴)ㆀ 제로님 것과 거의 비슷....
NVL과 CONNECT_BY_ROOT를 DECODE와 ROWNUM 쓴 차이!

단순 2레벨이라면 위와 같이 방법도 가능하겟지요.
3레벨 넘어간다면 다른 방법을 찾으셔야 할듯.
http://www.gurubee.net/article/26066

WITH usetable AS
(
SELECT 1 no, '1-1' subject, 0 re_no, 1 order_num FROM dual
UNION ALL SELECT 2, '학용품', 1, 2 FROM dual
UNION ALL SELECT 3, '과자'  , 1, 2 FROM dual
UNION ALL SELECT 4, '2-1'   , 0, 1 FROM dual
UNION ALL SELECT 5, '소풍'  , 4, 2 FROM dual
UNION ALL SELECT 6, '병원'  , 1, 4 FROM dual
UNION ALL SELECT 7, '자전거', 4, 3 FROM dual
)
, detailtable AS
(
SELECT 2 no, 10 preAmt, 100 useAmt, 300 jusAmt FROM dual
UNION ALL SELECT 3,  50,  20,  30 FROM dual
UNION ALL SELECT 5, 200, 150, 200 FROM dual
UNION ALL SELECT 6,  60,  70,  20 FROM dual
UNION ALL SELECT 7,  80, 300,  50 FROM dual
)
SELECT a.no
     , a.subject
     , preAmt
     , useAmt
     , jusAmt
     , useAmt - jusAmt 증감
  FROM (SELECT ROWNUM rn
             , no, subject
          FROM usetable
         START WITH re_no = 0
         CONNECT BY PRIOR no = re_no
         ORDER SIBLINGS BY order_num
        ) a
     , (SELECT no
             , SUM(preAmt) preAmt
             , SUM(useAmt) useAmt
             , SUM(jusAmt) jusAmt
          FROM (SELECT CONNECT_BY_ROOT m.no no
                     , preAmt, useAmt, jusAmt
                  FROM usetable m
                     , detailtable d
                 WHERE m.no = d.no(+)
                 CONNECT BY PRIOR m.no = m.re_no
                )
         GROUP BY no
        ) b
 WHERE a.no = b.no(+)
 ORDER BY a.rn
;

모두들 감사합니다
그런데 제가 질문을 잘못해서 그런지 다른분들이 알려주신대로 햇더니 안되네요
순서가 안맞아요
마농님이 알려주신대로 했더니 맞게는 나오는데 금액합계가 이상한점이 잇어서 새로운 글에서 다시 질문드립니다

이발렌체님 제로님 감사드립다
모두들 주말 잘보내세요