SQLServer数据库的几种分页查询

1、创建表USERS并且插入20条数据。

CREATE TABLE [dbo].[USERS]([ID] [int] IDENTITY(1,1) NOT NULL, [USERNAME] [varchar](50) NULL, CONSTRAINT [PK_USERS] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY];

insert into [dbo].[USERS] select 'user2' as [USERNAME];

2、TOP分页TOP（）。

3、 Row_Number()函数加BETWEEN

Row_Number() OVER(ORDER BY *) AS rowNum

rowNum BETWEEN 6  AND 10

4、SQL Server 2012及2012以上版本的OFFSET-FETCH筛选

--OFFSET 5 ROWS 表示跳过前面的行数 这里从第6行开始 -- FETCH NEXT 5 ROWS 表示取5条记录