SQL Server 分页 OFFSET/FETCH NEXT、ROW_NUMBER()

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

*
*
*

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
ROW_NUMBER()是一个SQL Server中的Window函数,用于为结果集的每一行分配一个连续的整数。它可以用来实现分页功能,根据指定的排序规则对结果集进行编号,并根据需要检索指定的页码和每页的行数。 举个例子,如果我们想要在某个表中按照addtime字段排序,并分页显示每页3行,我们可以使用ROW_NUMBER()函数结合OFFSETFETCH语句来实现。在SQL Server 2012及以上版本中,可以使用以下语法: SELECT p.id, p.accieptname, p.addtime FROM [dbo].[GongCaiOrder_a] p ORDER BY addtime OFFSET (pageindex-1) * pagesize ROWS FETCH NEXT pagesize ROWS ONLY 这将返回指定页码的数据,假设pageindex=2,pagesize=3,则返回第4行至第6行的数据。 除了使用OFFSETFETCH语句,还可以使用通用的分页方法。例如,假设我们要检索第2页的数据,每页显示3行,可以使用以下语句: SELECT TOP pagesize * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY addtime) AS ROWS, p.id, p.accieptname, p.addtime FROM [dbo].[GongCaiOrder_a] p ) temp WHERE ROWS > pagesize * (pageindex - 1) 这将返回第4行至第6行的数据,与前面的例子相同。在这种方法中,首先使用ROW_NUMBER()函数对结果集进行编号,然后在外部查询中使用WHERE子句来筛选出指定页码的数据。 综上所述,ROW_NUMBER()函数是SQL Server中常用的用于分页的函数,可以根据指定的排序规则为查询结果分配连续的行号,并结合OFFSETFETCH或通用的分页方法来检索指定页码的数据。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [SQL Serverrow_number函数的常见用法示例详解](https://download.csdn.net/download/weixin_38551205/13682709)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* *3* [SQLROW_NUMBER() OVER()的用法详解](https://blog.csdn.net/qq_46505849/article/details/119929644)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值