http://www.gurubee.net/lecture/2878
이문제를 풀고 있습니다.
해답에는 11g부터 제공되는 재귀쿼리를 사용해서 해설을 하셨는데 제가 사용하는 버전은 10g라 재귀쿼리가 존재하지 않습니다.
with t as (SELECT 1 cod, '배' name, 5000 amt FROM dual UNION ALL SELECT 2, '사과', 3000 FROM dual UNION ALL SELECT 3, '딸기', 2000 FROM dual UNION ALL SELECT 4, '참외', 2000 FROM dual UNION ALL SELECT 5, '자두', 1000 FROM dual) select substr(sys_connect_by_path(name, '+'),2) , substr(sys_connect_by_path(amt,'+'),2) from t connect by prior cod < cod ;
이 부분까지는 전개하였는데 여기서 브레이크 조건을 걸고 싶은데 어떤방식으로 생각을 해야하는 건가요??
WITH t AS ( SELECT 1 cod, '배' nam, 5000 amt FROM dual UNION ALL SELECT 2, '사과', 3000 FROM dual UNION ALL SELECT 3, '딸기', 2000 FROM dual UNION ALL SELECT 4, '참외', 2000 FROM dual UNION ALL SELECT 5, '자두', 1000 FROM dual ) SELECT SUBSTR(XMLAGG(XMLELEMENT(x, '+', p1, p3) ORDER BY lv).Extract('//text()'), 2) nams , SUBSTR(XMLAGG(XMLELEMENT(x, '+', p2, p3) ORDER BY lv).Extract('//text()'), 2) amts FROM (SELECT rn , p1 , p2 , DECODE(COUNT(*), 1, '', '*'||COUNT(*)) p3 , COUNT(*) cnt , MIN(lv) lv FROM (SELECT rn , lv , REGEXP_SUBSTR(p1, '[^+]+', 1, lv) p1 , REGEXP_SUBSTR(p2, '[^+]+', 1, lv) p2 FROM (SELECT ROWNUM rn , lv lvl , SYS_CONNECT_BY_PATH(nam, '+') p1 , SYS_CONNECT_BY_PATH(amt, '+') p2 FROM t CROSS JOIN (SELECT lv FROM (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9) WHERE lv <= 5000 / (SELECT MIN(amt) FROM t) ) START WITH lv = 1 CONNECT BY PRIOR lv + 1 = lv AND PRIOR cod <= cod ORDER SIBLINGS BY cod ) , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9) WHERE lv <= lvl ) GROUP BY rn, p1, p2 ) GROUP BY rn HAVING SUM(p2*cnt) = 5000 ;