데이터베이스 질문과 답변
안녕하세요.
connect by 구문에 대해 질의할 것이 있습니다.
아래와 같은 테이블이 있습니다.
Cnt Name Start_Date End_Date
===================================================
12 Crown 1999-01-12 1999-12-20
24 Bids 2001-03-23 2003-02-10
connect by를 사용해서 아래와 같이 만드려고 합니다.
Period Name
===================================================
1999-01 Crown
1999-02 Crown
...
1999-12 Crown
2001-03 Bids
...
2003-02 Bids
사용중인 DB는 9i 이며 저런 항목으로 몇개의 라인이 더 있는 상황입니다.
많은 도움 바랍니다. ㅠㅠ
WITH T AS (
SELECT 12 AS cnt, 'Crown' AS name, '1999-01-12' AS start_date, '1999-12-20' AS end_date FROM DUAL
UNION ALL SELECT 24, 'Bids', '2001-03-23', '2003-02-10' FROM DUAL
)
SELECT
yyyymm, name
FROM T RIGHT OUTER JOIN (
SELECT
DISTINCT TO_CHAR(TO_DATE(start_date, 'YYYY-MM-DD') + LEVEL - 1, 'YYYY-MM') AS yyyymm
FROM
DUAL, (SELECT MIN(start_date) AS start_date, MAX(end_date) AS end_date FROM T) T2
CONNECT BY
TO_DATE(start_date, 'YYYY-MM-DD') + LEVEL <= TO_DATE(end_date, 'YYYY-MM-DD') + 1
) T2
ON
T2.yyyymm >= TO_CHAR(TO_DATE(T.start_date), 'YYYY-MM')
AND T2.yyyymm <= TO_CHAR(TO_DATE(T.end_date), 'YYYY-MM')
ORDER BY
yyyymm
;
WITH T AS (
SELECT 12 CNT , 'Crown' NAME , TO_DATE('1999-01-12','YYYY-MM-DD') START_DATE , TO_DATE('1999-12-20','YYYY-MM-DD') AS END_DATE FROM DUAL UNION ALL
SELECT 24 CNT , 'Bids' NAME , TO_DATE('2001-03-23','YYYY-MM-DD') START_DATE , TO_DATE('2003-02-10','YYYY-MM-DD') AS END_DATE FROM DUAL
)
SELECT TO_CHAR(ADD_MONTHS(A.START_DATE,LV-1),'YYYY-MM') Period
, A.NAME
FROM T A , (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 100 ) B
WHERE A.CNT >= B.LV
ORDER BY NAME DESC , Period
/* 수정 */
WITH T AS (
SELECT 12 CNT , 'Crown' NAME , TO_DATE('1999-01-12','YYYY-MM-DD') START_DATE , TO_DATE('1999-12-20','YYYY-MM-DD') AS END_DATE FROM DUAL UNION ALL
SELECT 24 CNT , 'Bids' NAME , TO_DATE('2001-03-23','YYYY-MM-DD') START_DATE , TO_DATE('2003-02-10','YYYY-MM-DD') AS END_DATE FROM DUAL
)
SELECT TO_CHAR(ADD_MONTHS(A.START_DATE,LV-1),'YYYY-MM') Period
, A.NAME
FROM T A
--, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 100 ) B
, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= (SELECT SUM(CNT) FROM T) ) B
WHERE A.CNT >= B.LV
ORDER BY NAME DESC , Period
cnt 인덱스가있다면 아래와같이 바뀌는것이 더 좋겠네요~
SELECT SUM(CNT) FROM T
-> select max(cnt) from t
SUM 과 MAX는 기능이 달라서요.
SUM 과 MAX는 기능이 달라서요.
-> 위의 답변에 설명좀 들을 수 있을까요?
제가 깜박 착각했네요.. ^^*
WITH T AS (
SELECT 12 AS cnt, 'Crown' AS name, '1999-01-12' AS start_date, '1999-12-20' AS end_date FROM DUAL
UNION ALL SELECT 24, 'Bids', '2001-03-23', '2003-02-10' FROM DUAL
)
,TT( CNT, NAME, START_DATE, Period, ADD_CNT) AS(
SELECT CNT, NAME, START_DATE
, TO_CHAR(TO_DATE(START_DATE,'YYYY-MM-DD'),'YYYY-MM') Period
, 1 ADD_CNT
FROM T
UNION ALL
SELECT CNT, NAME, START_DATE
, TO_CHAR(ADD_MONTHS(TO_DATE(START_DATE,'YYYY-MM-DD'),ADD_CNT),'YYYY-MM') Period
, 1+ADD_CNT ADD_CNT
FROM TT
WHERE CNT >= 1+ADD_CNT
)
SELECT Period, NAME
FROM TT
ORDER BY Period
;
) 버튼을 클릭하여 작성 하시면 됩니다.