블로그 이미지
LifeisSimple

calendar

    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30      

Notice

2016. 4. 6. 14:32 Brain Trainning/DataBase

Sql server  버전별 페이징 쿼리


출처 : http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/



USE AdventureWorks2012
GO
--------------------------------------------------
-- SQL Server 2012
--------------------------------------------------
DECLARE @RowsPerPage INT = 10@PageNumber INT = 6
SELECT SalesOrderDetailIDSalesOrderIDProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET 
(@PageNumber-1)*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO



--------------------------------------------------
-- SQL Server 2008 / R2
-- SQL Server 2005
--------------------------------------------------
DECLARE @RowsPerPage INT = 10@PageNumber INT = 6
SELECT SalesOrderDetailIDSalesOrderIDProductID
FROM (
SELECT SalesOrderDetailIDSalesOrderIDProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailIDAS RowNum
FROM Sales.SalesOrderDetail AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
GO


--------------------------------------------------
-- SQL Server 2000
--------------------------------------------------
DECLARE @RowsPerPage INT = 10@PageNumber INT = 6
SELECT SalesOrderDetailIDSalesOrderIDProductID
FROM
(
SELECT TOP (@RowsPerPage)
SalesOrderDetailIDSalesOrderIDProductID
FROM
(
SELECT TOP ((@PageNumber)*@RowsPerPage)
SalesOrderDetailIDSalesOrderIDProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
AS SOD
ORDER BY SalesOrderDetailID DESC
AS SOD2
ORDER BY SalesOrderDetailID ASC
GO



posted by LifeisSimple

댓글을 달아 주세요