22,209
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
id INT IDENTITY(1,1) PRIMARY KEY
,[Name] NVARCHAR(10)
,[Sex] NVARCHAR(2)
,[CsSort] VARCHAR(6)
,[Times] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'张三',N'男',N'2',N'2018')
INSERT INTO dbo.[t] VALUES(N'李四',N'男',N'2',N'2018')
INSERT INTO dbo.[t] VALUES(N'王五',N'男',N'1',N'2018')
INSERT INTO dbo.[t] VALUES(N'沈六',N'女',N'9',N'2018')
INSERT INTO dbo.[t] VALUES(N'宋奇',N'男',N'6',N'2017')
INSERT INTO dbo.[t] VALUES(N'诸葛',N'女',N'2',N'2015')
DECLARE @pageIndex INT,@pageSize INT
SET @pageIndex=1
SET @pageSize=2
--排序字段必须唯一, 否则会导致每页记录不稳定
--无法唯一的,可以加主键或其它字段保持唯一
--SQL Server2012+
SELECT id,[Name],Sex,CsSort,Times FROM t
ORDER BY t.CsSort,id
OFFSET (@pageIndex-1)*@pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;
/*
id Name Sex CsSort Times
----------- ---------- ---- ------ -----------
3 王五 男 1 2018
1 张三 男 2 2018
*/
--SQL Server2005+
SELECT * FROM (
SELECT ROW_NUMBER() OVER ( ORDER BY CsSort,id) AS rid,id,[Name],Sex,CsSort,Times
FROM t
)AS tt
WHERE rid BETWEEN (@pageIndex-1)*@pageSize AND @pageIndex* @pageSize
ORDER BY rid
/*
rid id Name Sex CsSort Times
-------------------- ----------- ---------- ---- ------ -----------
1 3 王五 男 1 2018
2 1 张三 男 2 2018
*/