Sql Server三种分页方法

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = ‘StuWebName‘)
DROP TABLE StuWebName
GO
CREATE TABLE StuWebName
(
StuId INT IDENTITY(1,1) PRIMARY KEY,
StuName VARCHAR(50) NOT NULL,
StuPassword VARCHAR(50) NOT NULL,
StuSex CHAR(2) DEFAULT(‘男‘),
StuNo INT NOT NULL
)
GO
DECLARE @ID INT
SET @ID=1
WHILE(@ID<100)
BEGIN
    INSERT INTO StuWebName(StuName,StuPassword,StuSex,StuNo)VALUES(‘张三‘+CONVERT(VARCHAR(50),@ID),‘123‘,‘男‘,0902)
    SET @ID=@ID+1
END
GO
select * from StuWebName
GO
-----------------------------------------------------
--几种最常见的排序排序(第一页每页五条数据)
-----------------------------------------------------
--先用row_number函数在每列加序号然后排序
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY StuId) AS b,* FROM StuWebName) AS a
WHERE b>(2-1)*5 and b<=2*5 
GO
--先查询前10行,然后先倒序在取前5行,最后在倒序(将数据倒回来)
SELECT * FROM (SELECT TOP 5 * FROM
(SELECT TOP 10 * FROM StuWebName ORDER BY StuId ASC) AS T 
ORDER BY StuId DESC)AS T 
ORDER BY StuId ASC
GO
--先查询前6行,让后取StuId的最大值
SELECT TOP 5 *
FROM StuWebName
WHERE (StuId >=
    (SELECT MAX(StuId)
    FROM (SELECT TOP ((5*1)+1) StuId
        FROM StuWebName 
        ORDER BY StuId) AS T)
)
ORDER BY StuId
GO

Sql Server三种分页方法

标签：