데이터베이스 질문과 답변
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 ;
이 부분까지는 전개하였는데 여기서 브레이크 조건을 걸고 싶은데 어떤방식으로 생각을 해야하는 건가요??
by jkson
[2017.12.18 13:01:18]
재귀쿼리는 중간까지 진행된 누적 sum을 구하기 쉬운데 connect by prior 문은 중간까지 진행된 누적 sum 구하기가 까다로울 것 같네요.
만약에 굳이 connect by prior 문으로 만들어야 한다면 전체 집합에서 최종적으로 5000 조합이 안 되는 것들을 빼야할 것 같은데..
음.. 쿼리 한 2달 거의 안 봤더니 감이 안 옵니다ㅠㅠ
by 마농
[2017.12.19 13:46:25]
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.