SQL Server 技术文档
方法1【offset/fetch next(SQL Server 2012及以上)】【强烈推荐】
select user_code from sys_user
where 1=1
order by user_code
offset 0 rows
fetch next 10 rows only
go
declare @pageIndex int = 1
declare @pageSize int = 10
select * from sys_user
where 1=1
order by user_code
offset (@pageIndex - 1) * @pageSize rows
fetch next @pageSize rows only
go
select user_code,row_number() over(order by user_code desc) as num from sys_user
where 1=1
order by user_code
offset 0 rows
fetch next 10 rows only
go
--存储过程分页
CREATE PROCEDURE [dbo].[proc_user_statistics_select]
@userName nvarchar(50),
@collectId nvarchar(50),
@startTime nvarchar(20),
@endTime nvarchar(20),
@pageIndex int,
@pageSize int
AS
BEGIN
declare @sql nvarchar(666)
declare @sqlWhere nvarchar(666) = N''
declare @nameSqlWhere nvarchar(666) = N''
if(@userName is not null)
begin
set @nameSqlWhere = ' where real_name like ''%'+@userName+'%'''
end
if(@collectId > 0)
begin
set @sqlWhere += ' and collect_id = ' + @collectId + ' '
end
if(@startTime is not null and @endTime is not null)
begin
set @sqlWhere += ' and s.created_time >= '''+@startTime+' 00:00:01'' and s.created_time <= '''+@endTime+' 23:59:59'' '
end
set @sql = 'select u.real_name as name,isnull(s.total,0) total from [dbo].[sys_user] u
left join (
select s.created_id,count(s.created_id) total from [dbNuclein].[dbo].[trans_screening] s
where 1=1 '+ @sqlWhere + '
group by s.created_id) s on s.created_id = u.id '
+ @nameSqlWhere +
' order by id desc
offset '+ convert(nvarchar(10),(@pageIndex - 1) * @pageSize) +' rows
fetch next '+ convert(nvarchar(10), @pageSize) +' rows only;'
set @sql += 'select count(id) total from [dbo].[sys_user] ' + @nameSqlWhere
exec sp_executesql @sql
--select u.real_name as name,isnull(s.total,0) total from [dbo].[sys_user] u
--left join (
--select s.created_id,count(s.created_id) total from [dbNuclein].[dbo].[trans_screening] s
--where 1=1 --and collect_id = 37 and s.created_time >= '2021-08-10 00:00:01' and s.created_time <= '2021-12-30 23:59:59'
--group by s.created_id) s on s.created_id = u.id
--where real_name like '%北%'
--order by id desc
--offset 0 rows
--fetch next 10 rows only
END
GO
方法2【ROW_NUMBER() OVER()】【SQL2012以下推荐使用】
SELECT TOP 页大小 * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY id desc) AS RowNumber,* FROM [表名] where 1=1) record
WHERE RowNumber > 页大小*(页数-1)
示例
--1
SELECT TOP 10 * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY id desc) AS RowNumber,* FROM asset where 1=1) record
WHERE RowNumber > 100
--2
select top 10 * from
(select ROW_NUMBER() OVER(ORDER BY id desc) AS RowNumber, * from (
select u.* from employees e left join users u on u.user_code=e.user_code where 1=1
)t) record
where RowNumber > 10
方法3
--语法
SELECT TOP 页大小 * FROM [表名]
WHERE id NOT IN (SELECT TOP 页大小*(页数-1) id FROM [表名] ORDER BY id desc)
ORDER BY id
--示例
SELECT TOP 10 * FROM pdt_Music
WHERE MusicID NOT IN(SELECT TOP 100 MusicID FROM pdt_Music ORDER BY MusicID desc)
ORDER BY MusicID desc
方法4
--语法
SELECT TOP 页大小 * FROM [表名]
WHERE id > (SELECT ISNULL(MAX(id),0) FROM (SELECT TOP 页大小*(页数-1) id FROM [表名] ORDER BY id desc) A)
ORDER BY id desc
--示例
SELECT TOP 10 * FROM pdt_Music
WHERE MusicID > (SELECT MAX(MusicID) FROM (SELECT TOP 100 MusicID FROM pdt_Music order by MusicID asc) as A)
ORDER BY MusicID asc
*
*
*