[MS SQL] MS SQL 2000 分頁

MS SQL 2005 以後要做分頁，有ROW_NUMBER() 可用，

但有些案子還是用SQL 2000，所以可以用以下的方式做分頁。

--產生測試資料---------------------------
CREATE TABLE #myTable(
 myID int IDENTITY(1,1) NOT NULL,
 myName nvarchar(10),
 visitTimes int
)

INSERT INTO #myTable 
SELECT 'MIB星際戰警',5000 UNION ALL
SELECT '記得愛記得愛',6000 UNION ALL
SELECT '愛‧重來',500 UNION ALL
SELECT '瀕臨邊緣的人',5421 UNION ALL
SELECT '愛情大凸搥',45613 UNION ALL
SELECT '大開影界全球',1247 
----------------------------------------

DECLARE @PageSize int, @PageIndex int;
SET @PageSize = 3; --分頁大小
SET @PageIndex = 0; --第0+1頁(分頁索引，從0開始)

CREATE TABLE #TmpTable(
 ID int IDENTITY (1,1) NOT NULL,
 myID int
)

INSERT INTO #TmpTable 
SELECT myID FROM #myTable ORDER BY visitTimes DESC

SELECT TT.ID, mT.myName, mT.visitTimes
FROM #TmpTable TT INNER JOIN #myTable mT ON TT.myID = mT.myID
WHERE TT.ID BETWEEN 
(@PageIndex * @PageSize + 1) AND ((@PageIndex+1)*@PageSize)
ORDER BY TT.ID ASC

DROP TABLE #myTable
DROP TABLE #TmpTable