데이터베이스 질문과 답변

Connect by 질문 입니다. 0 8 2,355

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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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]
?
1
2
3
4
5
6
7
8
9
10
11
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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
/* 수정 */
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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입