페이징 쿼리

페이징 쿼리 . 

1. NOT IN 쿼리

SELECT TOP 10 seq, thread, depth, writer, title, readcount, transdate
FROM ThreadBoard
WHERE seq NOT IN
(SELECT Top 10 * (@page-1) seq FROM ThreadBoard ORDER BY seq DESC)
ORDER BY thread DESC
http://taeyo.net/lecture/NET/NetBoard09.asp 참조

2. SELECT TOP n FROM (SELECT TOP n FROM ORDER BY ... ASC) DESC
SELECT *
FROM
(
SELECT TOP 10 *
FROM
(
SELECT TOP 20 seq, thread, depth, writer, title, readcount, transdate
FROM ThreadBoard
ORDER BY thread ASC
) AS A
ORDER BY thread DESC
) AS A

3. SQL Server 2005의 ROW_NUMBER() 함수 이용
use Adventureworks
go

SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid,
ModifiedDate
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS RowNum,
SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid,
ModifiedDate
FROM Sales.SalesOrderDetail
WHERE ProductID = 707
) AS A
WHERE RowNum BETWEEN 21 AND 40

출처 : http://www.taeyo.net/ 의 지용군님이 작성한글에서 발췌

내가 써본바로는 ROW_NUMBER()이 함수가 상당히 편하다.. 페이징 하기엔..