데이터베이스 질문과 답변

쿼리 질문있습니다~~ 0 3 621

by 아빠파파 [SQL Query] [2017.03.27 13:36:52]

안녕하세요~
지금 데이터가 이런식으로 나오는데요.

    A              B
-----------------------
20170326    2017.03
20170325    2017.03
20170324    2017.03
20170223    2017.02
20170201    2017.02
20170122    2017.01
20170103    2017.01

제가 원하는거는

    A              B
-----------------------
20170326    2017.03
20170325   
20170324    
20170223    2017.02
20170201   
20170122    2017.01
20170103   

 

이런식으로 중복되지 않게 나오게 하고싶습니다.
어떤 함수를 써야하는지 알려주시면 감사하게습니다~(_ _ ) 꾸벅

by swlee [2017.03.27 14:20:33] 

with t as (
select '20170326' dt,'2017.03' mon from dual union all
select '20170325','2017.03' from dual union all
select '20170324','2017.03' from dual union all
select '20170223','2017.02' from dual union all
select '20170201','2017.02' from dual union all
select '20170122','2017.01' from dual union all
select '20170103','2017.01' from dual
)
select dt, decode(rn,1,mon) mon
from (
select dt, mon, row_number() over(partition by substr(dt,1,6) order by dt desc) rn
from t)
order by dt desc

 

by 아빠파파 [2017.03.27 14:52:45]

감사합니다~^^

좋은하루되세요~

by 개발뉴비 [2017.03.27 14:35:14] 
with t as (
    select '20170326' dt,'2017.03' mon from dual union all
    select '20170325','2017.03' from dual union all
    select '20170324','2017.03' from dual union all
    select '20170223','2017.02' from dual union all
    select '20170201','2017.02' from dual union all
    select '20170122','2017.01' from dual union all
    select '20170103','2017.01' from dual
)
SELECT DT, MON
     , DECODE(MON, LAG(MON) OVER(ORDER BY DT DESC), '', MON)
  FROM T
 ORDER BY DT DESC

 

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