날짜 일련 생성 후 data_t 테이블에 조인을 어떻게 해야하나요? 0 1 171

by 이카루스 [SQL Query] oracle join [2020.01.14 18:19:03]


아래 data_t 테이블의 컬럼 중 yearmonthday 라는 년월일날짜데이타 들어가 있는데요. 비어있는 날짜를 다 채울 수 있게 조인 방법이 없을까요? ㅠㅠ

그러니깐 2019년 12월이랑  2020년 1월 일자까지 무조건 순차적으로 날짜일련(yearmonthday) 를 채우는 것입니다.



-- 소스 ---
with data_t as (

select 'T' as company, 'L' as code, 'host1' as server, 'TL_HOST2_11151' as serainum, '201912' as yearmonth, '20191215' as yearmonthday from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200105' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200129' from dual

)
select * from data_t;

-- 날짜 일련 2019년 12월1일부터 2020년 1월 31일까지 --
select to_char(to_date('20191201', 'yyyymmdd') + (level-1), 'yyyymmdd' ) as days
from dual connect by level <= 62;


-- 반영 결과 

with data_t2 as (

select 'T' as company, 'L' as code, 'host1' as server, 'TL_HOST2_11151' as serainum, '201912' as yearmonth, '20191201' as yearmonthday from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '201912', '20191202' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '201912', '20191203' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '201912', '20191204' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '201912', '20191205' from dual

union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '201912', '20191206' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '연속일련날짜--', '연속일련날짜.....' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200123' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200124' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200125' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200126' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200127' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200128' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200129' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200130' from dual
union all
select 'T' , 'L', 'host1' , 'TL_HOST2_11151', '202001', '20200131' from dual

)



select * from data_t2;

 

 

 

by 마농 [2020.01.15 08:11:11]
WITH data_t AS
(
SELECT 'T' company, 'L' code, 'host1' server, 'TL_HOST2_11151' serainum, '201912' yearmonth, '20191215' yearmonthday FROM dual
UNION ALL SELECT 'T', 'L', 'host1', 'TL_HOST2_11151', '202001', '20200105' FROM dual
UNION ALL SELECT 'T', 'L', 'host1', 'TL_HOST2_11151', '202001', '20200129' FROM dual
)
, days_t AS
(
SELECT TO_CHAR(TO_DATE('201912', 'yyyymm') + level - 1, 'yyyymmdd') days
  FROM dual
 CONNECT BY LEVEL <= 62
)
SELECT *
  FROM (SELECT DISTINCT company, code, server, serainum
          FROM data_t
         WHERE yearmonthday BETWEEN '20191201' AND '20200131'
        ) a
     , (SELECT SUBSTR(days, 1, 6) yearmonth
             , days yearmonthday
          FROM days_t
         WHERE days BETWEEN '20191201' AND '20200131'
        ) b
 ORDER BY company, code, server, serainum, yearmonth, yearmonthday
;

 

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