데이터베이스 질문과 답변

날짜 정렬 관련 쿼리 문의 0 4 907

by 권사마 [SQL Query] [2020.11.30 16:40:06]

안녕하세요~ 날짜 관련 쿼리 문의가 있습니다.

고수님들의 고견 부탁드립니다. 감사합니다.

 

* 샘플쿼리

select '20200504' as dt1, '10' as dt2 from dual union all
select '20200507' as dt1, '20' as dt2 from dual  union all
select '20200727' as dt1, '30' as dt2 from dual  union all
select '20200728' as dt1, '30' as dt2 from dual  union all
select '20200729' as dt1, '30' as dt2 from dual  union all
select '20200730' as dt1, '30' as dt2  from dual

 

* 문의사항

1. 데이터는 일자별로 날자가 들어가 있는데 연속된 날짜는 From~To 로 한 row로 보이고 싶습니다.

2. 조건 ( DT2 가 같은것들만 From~To로)

기존 값    
DT1 DT2  
20200504 10  
20200726 20  
20200727 30  
20200728 30  
20200729 30  
20200730 30  
원하는 값    
DT_From DT_TO DT2
20200504 20200504 10
20200726 20200507 20
20200727 20200730 30
by pajama [2020.11.30 17:05:25]

어..아래 표에는 20200726이 있는데..처음에 보여주신 쿼리로 해봤습니다.

with t as (
select '20200504' as dt1, '10' as dt2 from dual union all
select '20200507' as dt1, '20' as dt2 from dual  union all
select '20200727' as dt1, '30' as dt2 from dual  union all
select '20200728' as dt1, '30' as dt2 from dual  union all
select '20200729' as dt1, '30' as dt2 from dual  union all
select '20200730' as dt1, '30' as dt2  from dual
)
select min(dt1) dt_from, max(dt1) dt_to, dt2 from t group by dt2

 

by 마농 [2020.12.01 00:49:29]
by 권사마 [2020.12.01 17:54:59]

with t as (
select '20200504' as dt1, '10' as dt2 from dual union all
select '20200507' as dt1, '20' as dt2 from dual  union all
select '20200727' as dt1, '30' as dt2 from dual  union all
select '20200728' as dt1, '30' as dt2 from dual  union all
select '20200729' as dt1, '30' as dt2 from dual  union all
select '20200730' as dt1, '30' as dt2 from dual  union all
select '20200802' as dt1, '30' as dt2 from dual  union all
select '20200803' as dt1, '30' as dt2 from dual  union all
select '20200804' as dt1, '30' as dt2 from dual 
)

기존 값

 　 　
DT1 DT2 　
20200504 10 　
20200726 20 　
20200727 30 　
20200728 30 　
20200729 30 　
20200730 30 　
20200802 30 　
20200805 30 　
20200806 30 　
원하는 값 　 　
DT_From DT_TO DT2
20200504 20200504 10
20200726 20200507 20
20200727 20200730 30
20200802 20200802 30
20200805 20200806 30

쿼리 감사합니다. 제가 원하는 것은 위처럼 dt1 날짜별로 연속되는 날짜만 그룹지어서 보고 싶은건데요..  연속이 안되는 것들은 하나씩만 보이고..

혹시 이건 쿼리 가능할까요?

by 마농 [2020.12.01 18:38:34]

네. 가능합니다. 위 댓글 URL 참조.

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