Database
[ORACLE]그룹함수로 중복된 컬럼 제거하기
애플자라
2017. 11. 8. 15:16
반응형
-- 2건이상 데이터
SELECT MAX(ID) AS ID, MAX(TIMS_DATE) AS TIMS_DATE, MAX(FUND_CODE) AS FUND_CODE, COUNT(*) AS CNT
FROM IFT_FUND_STANDARD_MODIFY
WHERE FUND_CODE ='01032'
GROUP BY TIMS_DATE||FUND_CODE
HAVING COUNT(*) > 1
-- 2건이상 데이터중 과거 데이터 삭제
DELETE -- SELECT꼭 해보고 날려버린다
FROM IFT_FUND_STANDARD_MODIFY
WHERE TIMS_DATE||FUND_CODE
IN
(
SELECT TIMS_DATE||FUND_CODE
FROM IFT_FUND_STANDARD_MODIFY
WHERE FUND_CODE ='01032'
GROUP BY TIMS_DATE||FUND_CODE
HAVING COUNT(*) > 1
)
AND ID NOT IN (
SELECT MAX(ID) as ID
FROM IFT_FUND_STANDARD_MODIFY
WHERE FUND_CODE ='01032'
GROUP BY TIMS_DATE||FUND_CODE
HAVING COUNT(*) > 1
)
CONCAT(TIMS_DATE,FUND_CODE) 도 가능
반응형