ROW_NUMBER 이용한 쿼리문(페이징처리 쿼리문)

템플릿

SELECT T.*
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY 기준_컬럼명) RNUM
    ,컬럼1,컬럼2,컬럼3,컬럼4
    FROM 테이블명
    WHERE 1=1
)T
WHERE T.RNUM BETWEEN 시작숫자 AND 끝숫자
;

예시

SELECT T.*
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY EMP_NO) RNUM
    ,EMP_NO, EMP_NAME, EMP_ADDRESS, EMP_TELNO, EMP_SALARY, FILENAME
    FROM EMPLOYEE
    WHERE 1=1
)T
WHERE T.RNUM BETWEEN 1 AND 10
;

페이징 처리할 때 사용

		SELECT T.*
		FROM
		(
			SELECT ROW_NUMBER() OVER(ORDER BY BOOK_ID DESC) RNUM
			        ,BOOK_ID, TITLE, CATEGORY, PRICE, INSERT_DATE
			FROM   BOOK
			WHERE  1 = 1
			<include refid="where"></include>
		) T
		WHERE T.RNUM BETWEEN ((#{currentPage}*10)-(10-1)) AND (#{currentPage}*10)