SQL取m到n条数据；数据库分页

 /*取表中5--8条数据*/
SELECT TOP 4 EmployeeID FROM dbo.Employees WHERE EmployeeID NOT IN(
SELECT TOP 4 EmployeeID FROM dbo.Employees Order  BY EmployeeID ASC)

SELECT TOP 4 EmployeeID FROM
(SELECT TOP 8 EmployeeID FROM dbo.Employees ORDER BY EmployeeID ASC) a
ORDER BY EmployeeID DESC

--IDENTITY(INT)是在临时表上面添加一列用于标示原来的非连续的记录
--这种情况是原来的表中没有IDENTITY列
SELECT IDENTITY(INT) id0,* INTO #tmp1 FROM yo
SELECT * FROM #tmp1 WHERE id0>=5 AND id0<=8

--identitycol是一个关键字
SELECT * FROM yo WHERE identitycol BETWEEN 5 AND 8

--利用差集
SELECT TOP 8 * FROM yo
EXCEPT
SELECT TOP 4 * FROM yo

--数据库分页
DECLARE @pageSize INT
DECLARE @pageIndex INT
SET @pageSize=4
SET @pageIndex=2
SELECT TOP (@pageSize) yoID FROM yo WHERE yoID NOT IN
(SELECT TOP (@pageSize*(@pageIndex-1)) yoID FROM yo ORDER BY yoID)
ORDER BY yoID

DECLARE @pageSize INT
DECLARE @pageIndex INT
SET @pageSize=4
SET @pageIndex=2
SELECT TOP (@pageSize) yoID FROM yo
WHERE yoID >
(SELECT ISNULL(MAX(yoID),0) FROM
(SELECT TOP (@pageSize*(@pageIndex-1)) yoID FROM yo ORDER BY yoID) a ) ORDER BY yoID

--08/05平台上使用;另一个排序的是rank() over 是一个跳跃排序
--这样的程序在面对数据量大的情况下可以使用
DECLARE @pageSize INT
DECLARE @pageIndex INT
SET @pageSize=4
SET @pageIndex=2
SELECT TOP (@pageSize) * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY yoID) AS RowNumber,* FROM yo) a
WHERE RowNumber>(@pageSize*(@pageIndex-1))

DECLARE @pageSize INT
DECLARE @pageIndex INT
SET @pageSize=4
SET @pageIndex=2
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY yoID) AS rownum,* FROM yo) AS y
WHERE rownum BETWEEN  (@pageSize*(@pageIndex-1))+1 AND (@pageSize*@pageIndex)