Microsot SQL 2005에서 쉬운 페이징 쿼리문

Microsot SQL 2005에서 쉬운 페이징 쿼리 문

SQL 2005에서는페이징방법을새롭게적용할수있습니다.

또한쿼리구문을복잡하게사용하시던분들은SQL 2005에서개발한다면

아래와같이쉽게사용하시기바랍니다.

 

 

DECLARE@CURRPAGE INT

DECLARE@MAXPAGE  INT

 

SET@CURRPAGE=[현페이지주소]

SET@MAXPAGE =[페이지당출력수]

 

 

-- 1. WITH구문을이용해서하는방법

WITHTEMP_TABLEAS

(

       -- ====================================================================

       SELECT

                       ROW_NUMBER()OVER(ORDERBY[소트할내용])ASRowNum,

                      *

              FROM

                       [테이블명]

       -- ====================================================================

)

SELECT*FROMTEMP_TABLEWHEREROWNUMBETWEEN((@CURRPAGE-1)*(@MAXPAGE+1))AND(@CURRPAGE*@MAXPAGE)

 

-- 2. FROM를이용해서하는방법

SELECT

       *

       FROM

              (

                      -- ====================================================================

                      SELECT

                                      ROW_NUMBER()OVER(ORDERBY[소트할내용])asRowNum,

                                     *

                             FROM

                                      [테이블명]

                      -- ====================================================================

              )ASA

WHERE ROWNUM BETWEEN ( (( @CURRPAGE - 1 )*@MAXPAGE) + 1  ) AND ( @CURRPAGE * @MAXPAGE )

 

--WHEREROWNUMBETWEEN((@CURRPAGE-1)*(@MAXPAGE+1))AND(@CURRPAGE*@MAXPAGE)

 

 

출처 : http://msmvp.egloos.com/3453597