최대,최소값을 제외한 자료 조회(3건이상에서)

SELECT A.*
  FROM (SELECT SABUN,
               SDATE,
               ROW_NUMBER () OVER (PARTITION BY SABUN ORDER BY SDATE)
                                                                    AS RN_MIN,
               ROW_NUMBER () OVER (PARTITION BY SABUN ORDER BY SDATE DESC)
                                                                    AS RN_MAX,
               COUNT (SABUN) OVER (PARTITION BY SABUN) AS RN_CNT
          FROM TB_100
         WHERE COM_CD = 'AA'
           AND '20071001' <= SDATE) A
 WHERE CASE WHEN RN_CNT >= 3 THEN A.RN_MIN
                     ELSE 1
            END 
            <> 
            CASE WHEN RN_CNT >= 3 THEN 1
                     ELSE 2
            END
    AND CASE WHEN RN_CNT >= 3 THEN A.RN_MAX
                    ELSE 1
           END 
           <>
           CASE WHEN RN_CNT >= 3 THEN 1
                     ELSE 2
           END
;

sabun단위로 partition이 설정되므로 sabun단위의 건수가 3건 미만일 경우

where절의 조건이 1 <> 2 가 되므로 항상 true가 됩니다.

3건이 넘어서면 rn_min,rn_max는 1의 값을 가지며 1 <> 1가 되므로 항상

 false의 결과가 나오면서 최대,최소값을 제외할수 있게 됩니다.

SELECT A.*
  FROM (SELECT SABUN,
               SDATE,
               ROW_NUMBER () OVER (PARTITION BY SABUN ORDER BY SDATE)
                                                                    AS RN_MIN,
               ROW_NUMBER () OVER (PARTITION BY SABUN ORDER BY SDATE DESC)
                                                                    AS RN_MAX,
               COUNT (SABUN) OVER (PARTITION BY SABUN) AS RN_CNT     
          FROM TB_100
         WHERE COM_CD = 'AA'
           AND '20071001' <= SDATE) A
 WHERE DECODE (SIGN (A.RN_CNT-3), -1, 1, A.RN_MIN)
            <>
            DECODE (SIGN (A.RN_CNT-3),-1, 2,1)
     AND DECODE (SIGN (A.RN_CNT-3), -1, 1, A.RN_MAX)
            <>
            DECODE (SIGN (A.RN_CNT-3),-1, 2,1)
;