데이터베이스 질문과 답변
날짜 일련 생성 후 data_t 테이블에 조인을 어떻게 해야하나요?
0
1
3,299
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.