SQL四种分页方式

--sp_detach_db ''

use master

go

if db_id('User_DB') is not null

drop database  User_DB

go

create database  User_DB

go

use User_DB

go

create table LoginInfo

(

Lid int identity (1,1) primary key ,

LoginName nvarchar(50),

LoginPwd varchar(50),

RegTime datetime default( getdate())

)

go

create table UserInfo

(

Uid int identity (1,1) primary key ,

Sex int default(1) ,

Age int,

MyMoney decimal, 

U_id int references LoginInfo (Lid)

 

)

declare @i int

declare @j int 

set @i =1

set @j =1

while @i<20

--   begin

--    insert into  LoginInfo values ('User_'+convert(varchar,@i)+'','Pwd_'+convert(varchar,@i)+'',default)

--    set @i = @i+1

--   end

--select Lid, LoginName, LoginPwd, RegTime from LoginInfo

      if convert(int ,rand()*10)%2 =1

     begin

       set @j = convert(int ,rand()*10)

      insert into UserInfo values(1,@j,10+@j,@i)

      set @i = @i+1

     end

    else

    begin

     set @j = convert(int ,rand()*10)*-1

     insert into UserInfo values(1,20+@j,10+@j,@i)

     set @i = @i+1

    end

go

select Uid, Sex, Age, MyMoney, U_id from UserInfo

select LoginName,Sex,Age,MyMoney, RegTime from LoginInfo,UserInfo where LoginInfo.Lid= UserInfo.U_id

go

--模糊查询带分页存储过程

create proc Sp_Page

@LoginName nvarchar(50),

@Sex int ,

@AgeBegin int ,

@AgeEnd int ,

@PageCount int,

@PageIndex int,

@AllCount int output

 

as

 select LoginName, Sex,Age,MyMoney ,RegTime from

(

select row_Number() over (order by Age asc ) as P_Id ,* from  Logininfo, UserInfo

where Logininfo.Lid = UserInfo.U_id

     and(Sex>0)

    and (len(@LoginName)=0 or charindex(@LoginName,@LoginName)>0)

    and (Age between @AgeBegin and @AgeEnd  )

)as p

 

 where  P_Id  between @PageCount*(@PageIndex-1)+1 and @PageCount*@PageIndex

  select @AllCount = count(Uid) from UserInfo

     where (Sex>0)

           and (Age between @AgeBegin and @AgeEnd  )

           and (len(@LoginName)=0 or charindex(@LoginName,@LoginName)>0)

 

declare @count int

exec Sp_Page'',0,0,50,10,1,@count output

print @count

 

-------------------------------------

--方案一：(利用Not In和SELECT TOP分页)

SELECT TOP 5 *

FROM LoginInfo

WHERE (Lid  NOT IN

          (SELECT TOP (5*(1-1)) Lid

         FROM LoginInfo

      ORDER BY Lid  ))

ORDER BY Lid

 

--SELECT TOP 页大小*

--FROM TestTable

--WHERE (ID NOT IN

--          (SELECT TOP (页大小*(页数-1)) id

--         FROM 表

--         ORDER BY id))

--ORDER BY ID

-------------------------------------

--方案二：(利用ID大于多少和SELECT TOP分页）

 

SELECT TOP 5 *

FROM LoginInfo

WHERE (Lid >

         (SELECT MAX(Lid)

         FROM (SELECT TOP (5*(1-1)) Lid

                 FROM LoginInfo

                 ORDER BY Lid) AS T))

ORDER BY Lid

 

print 5*0

--SELECT TOP 页大小*

--FROM TestTable

--WHERE (ID >

--          (SELECT MAX(id)

--         FROM (SELECT TOP  (页大小*(页数-1)) id

--                 FROM 表

--                 ORDER BY id) AS T))

--ORDER BY ID

 

-------------------------------------

--分页方案三：(利用SQL的游标存储过程分页)

if object_id ('XiaoZhengGe') is not null

drop proc XiaoZhengGe

go

create  procedure XiaoZhengGe

@sqlstr nvarchar(4000), --查询字符串

@currentpage int, --第N页

@pagesize int --每页行数

as

set nocount on

declare @P1 int, --P1是游标的id

 @rowcount int

exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output

select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页

 

set @currentpage=(@currentpage-1)*@pagesize+1

exec sp_cursorfetch @P1,16,@currentpage,@pagesize

exec sp_cursorclose @P1

set nocount off

 -------------------------------------

----分页方案四：(行号)

  select LoginName, Sex,Age,MyMoney ,RegTime from

(

select row_Number() over (order by Age asc ) as P_Id ,* from  Logininfo, UserInfo

)as p

 where  P_Id  between 5*(1-1)+1 and 5*1

 

 

 

/***********************************************************************

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。

建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:

分页方案一：(利用Not In和SELECT TOP分页)   效率一般，需要拼接SQL语句

分页方案二：(利用ID大于多少和SELECT TOP分页）效率高，需要拼接SQL语句

分页方案三：(利用SQL的游标存储过程分页)    效率最差，通用

分页方案四：(利用SQL行号分页)    效率最高，最为通用

*************************************************************************/