Sqlserver 分页相关

小黄瓜要编程
249 阅读4分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第4天，点击查看活动详情

 SqlServer 分页的存储过程

方法一 取数据30-40 先取前40 再取前10且不存在前30中

<pre name="code" class="sql"><p>if exists(select 1 from sysobjects where name='P_GetUserInfoByPage' and type='p')
drop proc P_GetUserInfoByPage//检查是否存在命名存储过程，存在则删除
go
create proc P_GetUserInfoByPage
(
@pageIndex int,--分页的索引，默认为1（查看的页数）
@pageSize int ,--分页的大小,每一页的数据条数
@pageCount int out --总的分页数 
)
as
declare @endNum int, ---数据截止量
        @sql varchar(500),--命令sql语句的长度
        @recordCount int--总的数据条数
              
select @recordCount=COUNT(*)from tb_userinfo
--set @pageCount =ceiling(@recordCount *1/@pageSize )--
set @pageCount =ceiling (@recordCount*1.0/@pageSize )--ceiling进一法取值 必须将其精度提高到0.1
if(@pageIndex>@pageCount)
set @pageIndex=@pageCount
set @endNum=(@pageIndex -1)*@pageSize--查看的页数的前一页的最后一条数据
set @sql='select top '+convert(varchar(20),@pageSize)+' * from tb_userinfo where userid 
not in(select top '+convert(varchar(20),@endNum)+'userid from tb_userinfo)'
exec (@sql)
go
declare @pageCount int
exec P_GetUserInfoByPage 3,3,@pageCount out
print @pageCount 
select top 6* from tb_userinfo where userid 
not in(select top 12 userid from tb_userinfo)
go
--SQL SERVER 2008 无法启动T-SQL调试
--exec sp_addsrvrolemember 'SDWM-20150327QS\Administrator', 'sysadmin'
--分页方法2
--开窗函数  取30-40 先对所有的数据进行排序 再取30-40
if exists(select * from sysobjects where name='P_GetUserInfoListByPage' and type='p')
drop proc P_GetUserInfoListByPage
go
create proc P_GetUserInfoListByPage
(
@pageIndex int,
@pageSize int
)
as
declare @statrNum int,
	@eneNum int
set @statrNum=(@pageIndex-1)*@pageSize+1
set @eneNum=@pageIndex*@pageSize
select * from(select ROW_NUMBER () Over(order by userid) as rownum, * from tb_userinfo )as temp
where temp.rownum between @statrNum and @eneNum
go
exec P_GetUserInfoListByPage 2,6
exec P_GetUserInfoByPage 2,6
-----带条件的查询
if exists(select * from sysobjects where name='P_getListUserInfoByCondition' and type ='p')
drop proc P_getListUserInfoByCondition
go
create proc P_getListUserInfoByCondition
( @userid int,
  @username varchar(30),
  @roleid int,
  @pageIndex int,
  @PageSize int,
  @pageCount int out
  )
  as
  declare @recordCount int,--表示满足条件的数据总数
  --拼接查询字符串
  @sql nvarchar(3000),
  @sqlconditional nvarchar(1000)
  set @sqlconditional='';
  set @sql ='select @rowcount=count(*) from tb_userinfo where 1=1';--查询语句 1=1格式匹配where
  if  @userid is not null and @userid >0
  set @sqlconditional =@sqlconditional+ 'and userid='+CONVERT (nvarchar(10),@userid)+'';
  if  @username is not null and @username <> N''--N Unicode格式字符匹配
  set @sqlconditional=@sqlconditional+ 'and username like''%'+@username+'%'' ';
  if  @roleid is not null and @roleid >0
  set @sqlconditional=@sqlconditional+'and roleid ='+CONVERT (nvarchar(20),@roleid)+'';
  set @sql=@sql +@sqlconditional 
  exec sp_executesql @sql,N'@rowcount int out',@recordCount out--sp_executesql
  set @pageCount=CEILING (@recordCount *1.0/@PageSize);
  declare
	 @statrNum int,
	 @endNum int,
	 @sqlpage nvarchar(4000)
 set @statrNum=(@pageIndex-1)*@pageSize+1;
 set @endNum=@pageIndex*@pageSize
 set @sqlpage =' Select ROW_NUMBER() over(order by userid) as rcount,* From tb_UserInfo  Where 1=1 ';
 set @sqlpage = @sqlpage + @sqlconditional;--把sql拼接的查询条件组合在一起
 set @sqlpage = 'Select * From ('+@sqlpage+') as t where t.rcount between '+convert(varchar(20),@statrNum)+
 ' and '+convert(varchar(20),@endNum)+' ';
 exec sp_executesql @sqlpage
 go
 
 declare @pageCount int
 exec P_getListUserInfoByCondition null,'a',null,1,100,@pageCount out
 print @pageCount</p><h2>--通用存储分页（开窗函数）</h2>if exists (select 1 
<span style="white-space:pre">		</span>   from sysobjects 
<span style="white-space:pre">		</span>   where [name]='proc_CommonPage' 
<span style="white-space:pre">		</span>   and [type]='p')
   drop proc proc_CommonPage
go
create proc proc_CommonPage
( @pageSize int,--分页大小（每一页多少）
  @pageIndex int,--分页索引（页数）
  @tablename varchar(50),--表名
  @prikey varchar(20),--查询关键字
  @condtional varchar(2000),--查询条件
  @pageCount int out --总页数
)
as
--构建查询的sql语句
declare @sql nvarchar(1000);--查询的sql
declare @recordCount int,--数据总数
        @startIndex int,--分页的启示行  
<span style="white-space:pre">		</span>@endIndex int--分页的结束行
set @sql='select @recordCount=count(*) from '+@tablename+' where 1=1 ';
if<span style="white-space:pre">	</span>@condtional is not null and @condtional<> N''--查询条件不为空 也不为空格
begin
set @sql=@sql+@condtional--把查询条件加入sql语句
end
print @sql
exec sp_executesql @sql,N'@recordCount int out',@recordCount out;
 --计算最大的分页数--
 set @pageCount = CEILING(@recordcount*1.0/@pagesize);--最大的分页数 进一法取值
 if @pageindex > @pageCount   --如果分页的索引大于最大的索引
 begin
    set @pageindex = @pageCount;
 end
 --构建分页的查询的起始行和结束行
 set @startIndex = (@pageindex-1)*@pagesize +1;--计算起始行
 set @endIndex = @pageindex *@pagesize
 --构建分页的sql
 set @sql =''
 set @sql = 'Select row_number()  over (order by '+@prikey+' asc) as rowid,*  from '+@tablename+' where 1=1';
 if @condtional is not null and @condtional <> N''   --如果查询条件不为空
 begin
    set @sql = @sql+ @condtional;
 end
 set @sql = 'Select * From ('+@sql+') as tc where tc.rowid between '+convert(varchar(5),@startIndex)+' and '+convert(varchar(5),@endIndex)+'';
 exec (@sql)--查询语句
 go
 
  --测试
 declare @pagecount int
 exec proc_CommonPage 1,5,'tb_userinfo','userid','And usertrue=''aa'' and roleid=2',@pagecount out
 go
declare @recordCount int
select @recordCount=count(*) from tb_userinfo where 1=1 
pt

Sql 不返回受影响行数

在做sql通用分页的时候 经理告诉我一般不返回受影响的行数 我不知返回一个这个和没有有什么区别

我相信很多和我一样刚做开发的实习生都不知道吧（你知道了 我就说的不是你）

查看sql帮助得知 SET NOCOUNT ON的作用
当 SET NOCOUNT 为 ON 时，不返回计数。当 SET NOCOUNT 为 OFF 时，返回计数。
即使当 SET NOCOUNT 为 ON 时，也更新 @@ROWCOUNT 函数。
当 SET NOCOUNT 为 ON 时，将不向客户端发送存储过程中每个语句的 DONE_IN_PROC 消息。如果存储过程中包含一些并不返回许多实际数据的语句，网络通信流量便会大量减少，因此，将 SET NOCOUNT 设置为 ON 可显著提高性能。
SET NOCOUNT 指定的设置是在执行或运行时生效，而不是在分析时生效。
因此我们在开发过程，尤其是写存储过程的时候，当不需要返回消息时就可以使用 SET NOCOUNT ON 来提高我们程序的性能

avatar
文章
阅读
粉丝