너무 기초적인거 같지만 여쭤어 봅니다.
같은이름을 가진 사람들중 sdata값이 20171120에 sdata, data1 값으로 모두 변경 하려면
어떻게 해야 할까요?
== 원본데이터 ==
name sdate data1
홍길동 20171120 111
홍길동 20171119 1
홍길동 20171118 11
영구 20171119 1
영구 20171120 1111
== 결과 데이터 ==
name sdate data1
홍길동 20171120 111
홍길동 20171120 111
홍길동 20171120 111
영구 20171120 1111
영구 20171120 1111
WITH T(NAME,SDATE,DATA1) AS( SELECT '홍길동','20171120','1111' FROM DUAL UNION ALL SELECT '홍길동','20171119','1' FROM DUAL UNION ALL SELECT '홍길동','20171118','11' FROM DUAL UNION ALL SELECT '영구' ,'20171119','1' FROM DUAL UNION ALL SELECT '영구' ,'20171120','1111' FROM DUAL ) SELECT B.NAME,NVL(A.SDATE,B.SDATE),NVL(A.DATA1,B.DATA1) FROM ( SELECT NAME, MIN(SDATE) SDATE, MIN(DATA1) DATA1 FROM T WHERE SDATE = '20171120' GROUP BY NAME ) A, T B WHERE B.NAME = A.NAME(+)
-- 1. 조회 -- SELECT name , MAX(sdate) OVER(PARTITION BY name) sdate , FIRST_VALUE(data1) OVER(PARTITION BY name ORDER BY sdate DESC) data1 FROM t ; -- 2. 갱신 -- UPDATE t a SET (sdate, data1) = (SELECT MAX(sdate) sdate , MAX(data1) KEEP(DENSE_RANK LAST ORDER BY sdate) data1 FROM t b WHERE b.name = a.name ) ;