[ORACLE]그룹함수로 중복된 컬럼 제거하기

Database 2017.11.08 15:16 Posted by 애플자라

-- 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) 도 가능

 

출처 - http://linguist79.tistory.com/49

[ORACLE] 구분자_LIST형_맵핑형_조합형쿼리

Database 2009.11.04 18:10 Posted by 애플자라

* TB_INST_INPUT테이블에 SUBS_VALUE 데이터가 |구분자로 값이 들어오는 경우

-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 각각 매핍변수로 선언

SELECT SUB1,SUB2,SUB3,SUB4,SUB5,SUB6,SUB7,SUB8,SUB9,SUB10,SUB11,SUB12,SUB13,SUB14,
SUB15
    FROM(SELECT
            MAX(DECODE(rowno,1,aa)) SUB1
           ,MAX(DECODE(rowno,2,aa)) SUB2
           ,MAX(DECODE(rowno,3,aa)) SUB3
           ,MAX(DECODE(rowno,4,aa)) SUB4
           ,MAX(DECODE(rowno,5,aa)) SUB5
           ,MAX(DECODE(rowno,6,aa)) SUB6
           ,MAX(DECODE(rowno,7,aa)) SUB7
           ,MAX(DECODE(rowno,8,aa)) SUB8
           ,MAX(DECODE(rowno,9,aa)) SUB9
           ,MAX(DECODE(rowno,10,aa)) SUB10
           ,MAX(DECODE(rowno,11,aa)) SUB11
           ,MAX(DECODE(rowno,12,aa)) SUB12
           ,MAX(DECODE(rowno,13,aa)) SUB13
           ,MAX(DECODE(rowno,14,aa)) SUB14
           ,MAX(DECODE(rowno,15,aa)) SUB15
        FROM(
             SELECT SUBSTR( RCVDATA,
                    INSTR (RCVDATA, '|', 1, LEVEL) + 1,
                    INSTR (RCVDATA, '|', 1, LEVEL + 1) -
                    INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa,
                    ROWNUM AS rowno
               FROM (SELECT '|' || SUBS_VALUE ||'|' RCVDATA
                           ,ROWNUM
                       FROM TB_INST_INPUT
                       --WHERE INSTID = '@{EMS_M_ID}'
                       )
                    CONNECT BY LEVEL<= LENGTH(RCVDATA) -
                    LENGTH(REPLACE(RCVDATA, '|')) - 1
        )
        WHERE ROWNUM <= '15'
    )
WHERE ROWNUM = 1


-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 한개의 데이터로 조합


SELECT a1||a2||a3||a4||a5||a6||a7||a8||a9||a10||a11||a12||a13||a14  totaldata
    FROM(SELECT MAX(DECODE(rowno,1,aa)) a1
           ,MAX(DECODE(rowno,2,aa)) a2
           ,MAX(DECODE(rowno,3,aa)) a3
           ,MAX(DECODE(rowno,4,aa)) a4
           ,MAX(DECODE(rowno,5,aa)) a5
           ,MAX(DECODE(rowno,6,aa)) a6
           ,MAX(DECODE(rowno,7,aa)) a7
           ,MAX(DECODE(rowno,8,aa)) A8
           ,MAX(DECODE(rowno,9,aa)) a9
           ,MAX(DECODE(rowno,10,aa)) a10
           ,MAX(DECODE(rowno,11,aa)) a11
           ,MAX(DECODE(rowno,12,aa)) a12
           ,MAX(DECODE(rowno,13,aa)) a13
           ,MAX(DECODE(rowno,14,aa)) a14
        FROM(SELECT SUBSTR( RCVDATA,
                    INSTR (RCVDATA, '|', 1, LEVEL) + 1,
                    INSTR (RCVDATA, '|', 1, LEVEL + 1) -
                    INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa
                    ,ROWNUM AS rowno
               FROM (SELECT '|' || SUBS_VALUE || '|' RCVDATA
                           ,ROWNUM
                       FROM TB_INST_INPUT)
                    CONNECT BY LEVEL<= LENGTH(RCVDATA) -
                    LENGTH(REPLACE(RCVDATA, '|')) - 1)
    WHERE ROWNUM <= '14'
    )
   
   
-- SUBS_VALUE 컬럼의 데이터를 | 구분자로 짤라 리스트 데이터로 조합   

SELECT SUBSTR( RCVDATA,
INSTR (RCVDATA, '|', 1, LEVEL) + 1,
INSTR (RCVDATA, '|', 1, LEVEL + 1) -
INSTR (RCVDATA, '|', 1, LEVEL) - 1 ) aa
FROM (SELECT '|' || SUBS_VALUE || '|' RCVDATA FROM TB_INST_INPUT)
CONNECT BY LEVEL<= LENGTH(RCVDATA) -
LENGTH(REPLACE(RCVDATA, '|')) - 1;

참조 - 이동준형 마음ㅋ