꿈꾸는 개발자, DBA 커뮤니티 구루비

Connect by 질문 입니다.

by 바람이불면 [SQL Query] connect_by 질문 [2016.01.29 14:56:34]

안녕하세요.

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 이며 저런 항목으로 몇개의 라인이 더 있는 상황입니다.

많은 도움 바랍니다. ㅠㅠ

by 아발란체 [2016.01.29 15:36:03]

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
;

by 우리집아찌 [2016.01.29 15:39:31]

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

by 우리집아찌 [2016.01.29 15:42:12]

/* 수정 */
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

by 겸댕2후니 [2016.01.29 16:28:12]

cnt 인덱스가있다면 아래와같이 바뀌는것이 더 좋겠네요~

SELECT SUM(CNT) FROM T

-> select max(cnt) from t

by 우리집아찌 [2016.01.29 18:07:32]

SUM 과 MAX는 기능이 달라서요.

by 겸댕2후니 [2016.02.01 10:30:14]

SUM 과 MAX는 기능이 달라서요.

-> 위의 답변에 설명좀 들을 수 있을까요?

by 우리집아찌 [2016.02.01 10:33:30]

제가 깜박 착각했네요.. ^^*

by 창조의날개 [2016.01.29 16:37:29]


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
;

데이터베이스 질문과 답변