Sql Server 分页四种写法

– 只做分页
–1.第一种
create proc MyPaging1
(
@pageIndex int,
@pageSize int,
@totalRecordCount int output
)
as
begin
select top (@pageSize) * from GoodsInfo where id not in (select top (@pageSize * (@PageIndex-1)) Id from GoodsInfo)
select @totalPageCount = Count(*) from GoodsInfo
end

go

–第2种(只做分页)
create proc MyPaging2
(
@pageIndex int,
@pageSize int,
@totalPageCount int output
)
as
begin
–select top 5 * from GoodsInfo where id not in (select top 10 Id from GoodsInfo)
declare @s int ,@e int
set @s = @pageIndex * (@pageSize - 1)+1
set @e = @pageIndex * @pageSize

 select * from (select ROW_NUMBER() over (order by Id) as NO, * from GoodsInfo) T where T.NO between @s and @e

 declare @a decimal 
 select @a = Count(*) from GoodsInfo

 set @totalPageCount = CEILING( @a / @pageSize )
end
go

select * from GoodsInfo
declare @count int
exec MyPaging1 1,5,@count output
select @count
go

– 带固定查询条件的分页
create proc MyPaging3
(
@pageIndex int,
@pageSize int,
@Name varchar(50),
@price decimal,
@totalRecordCount int output
)
as
begin
select * into #Temp from GoodsInfo where Name like (’%’+@Name+’%’) and Price > @price
select top (@pageSize) * from #Temp where id not in (select top (@pageSize * (@PageIndex-1)) Id from #Temp)
select @totalRecordCount = Count(*) from #Temp
end

– 多个条件组合的分页(多条件查询时,不建议使用存储过程)
go
create proc MyPaging3
(
@pageIndex int,
@pageSize int,
@Name varchar(50),
@price decimal,
@totalRecordCount int output
)
as
begin
if len(@Name)=0 and @price =0
begin
select * into #Temp1 from GoodsInfo
select top (@pageSize) * from #Temp1 where id not in (select top (@pageSize * (@PageIndex-1)) Id from #Temp1)
select @totalRecordCount = Count() from #Temp1
end
else if len(@Name)=0 and @price>0
begin
select * into #Temp2 from GoodsInfo where price>@price
select top (@pageSize) * from #Temp2 where id not in (select top (@pageSize * (@PageIndex-1)) Id from #Temp2)
select @totalRecordCount = Count() from #Temp2
end
else if len(@Name)>0 and @price=0
begin
select * into #Temp3 from GoodsInfo where Name like (’%’+@Name+’%’)
select top (@pageSize) * from #Temp3 where id not in (select top (@pageSize * (@PageIndex-1)) Id from #Temp3)
select @totalRecordCount = Count() from #Temp3
end
else
begin
select * into #Temp4 from GoodsInfo where Name like (’%’+@Name+’%’) and Price>@price
select top (@pageSize) * from #Temp4 where id not in (select top (@pageSize * (@PageIndex-1)) Id from #Temp4)
select @totalRecordCount = Count() from #Temp4
end
end

declare @a int
exec MyPaging3 1,3,‘苹’,15,@a output
select @a

--------------------------------------------------
```csharp
--更新sql分页写法

Create Database MySchoolDB1220
go
use MySchoolDB1220
go
--年级表
create table Grade
(
Id int primary key identity,
Name varchar(50)
)
--班级表
create table Category
(
Id int primary key identity,
Name varchar(50),
GId int foreign key references Grade(Id) -- 年级ID
)
--学生表
create table Student
(
Id int primary key identity,
Name varchar(50),
Sex varchar(50),
Tel varchar(50),
Photo varchar(50),
JoinTime varchar(50),--入学时间
OutTime varchar(50),--毕业时间
CId int foreign key references Category(Id)--班级ID
)
go
insert into Grade values ('S1'),('S2'),('S3')
insert into Category values ('S1101',1),('S1102',1),('S2201',2),('S2202',2),('S3301',3),('S3302',3)
---------------------------------------------------------------------------------------------------------
go
--存储过程 -带固定条件的分页查询
create proc GetStudent_1
(
@PageIndex int,
@PageSize int,
@Cid int,
@PageCount int output
)
as 
begin 
   declare @s int = (@PageIndex -1)*@PageSize+1
   declare @e int = @PageIndex * @PageSize
   
   select @PageCount = Count(*) from Student where CId=@Cid
   select * from 
   (
   select 
   ROW_NUMBER() over (Order by C.Id) as No, 
   S.* ,
   C.Name as CName
   from Student S left join Category C 
   on C.Id = S.CId
   where C.Id = @Cid
   ) T
   where T.No between @s and @e
end 
go
-----------------------------------------------------------------------------
--组合条件查询 无分页
create proc GetStudent_2
(
@Cid int ,
@Name varchar(50),
@StartTime varchar(50),
@EndTime varchar(50)
)
as 
begin
	declare @sql nvarchar(2000) = N'select S.*,C.Name as CName from Student S left join Category C on S.Cid=C.Id where 1=1 ';
	if(@Cid>0)
	begin 
		set @sql = @sql + N' and S.Cid = '+@Cid
	end 

	if(len(@Name)>0)
	begin
		set @sql = @sql + N' and S.Name like '+@Name 
	end 

	if(len(@StartTime)>0)
	begin
		set @sql = @sql + N' and JoinTime > '''+@StartTime+'''' 
	end 


	if(len(@EndTime)>0)
	begin
		set @sql = @sql + N' and JoinTime < '''+@EndTime+'''' 
	end 

	exec sp_executesql @sql
end    
--------------------------------------------------------------------------------
go
--------------------------------------------------------------------------------
create proc GetStudent_3
(
@PageIndex int,
@PageSize int,
@Cid int,
@Name varchar(50),
@StartTime varchar(50),
@EndTime varchar(50),
@PageCount int output
)
as 
begin 
   declare @s int = (@PageIndex -1)*@PageSize+1
   declare @e int = @PageIndex * @PageSize
   declare @SqlCount nvarchar(2000)
   declare @sqlPage nvarchar(2000)

   declare @sql nvarchar(2000) = N'select S.*,C.Name as CName from Student S left join Category C on S.Cid=C.Id where 1=1 ';
	if(@Cid>0)
	begin 
		set @sql = @sql + N' and S.Cid = '+@Cid
	end 

	if(len(@Name)>0)
	begin
		set @sql = @sql + N' and S.Name like '+@Name 
	end 


	if(len(@StartTime)>0)
	begin
		set @sql = @sql + N' and JoinTime > '''+@StartTime+'''' 
	end 

	if(len(@EndTime)>0)
	begin
		set @sql = @sql + N' and JoinTime < '''+@EndTime+'''' 
	end 

	set @sql = '('+@sql+') T'
	
	set @SqlCount= N'Select @PageCount = Count(*) from '+@sql
	exec sp_executesql @SqlCount,N'@PageCount int out',@PageCount out

	set @sqlPage = N'select * from  ( select ROW_NUMBER() over (Order by T.Id) as No, * from '+@sql+') TN where TN.No between '+convert(varchar(5),@s)+' and '+convert(varchar(5),@e)+')'							  
	exec sp_executesql @sqlPage		
end 					   
go