select @Obj_ID=object_id(@QueryStr) ,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end ,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end
--如果显示第一页,可以直接用top来完成 if @PageCurrent=1 begin select @Id1=cast(@PageSize as varchar(20)) exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder) return end
--如果是表,则检查表中是否有标识更或主键 if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1 begin select @Id1=cast(@PageSize as varchar(20)) ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
select @FdName=name from syscolumns where id=@Obj_ID and status=0x80 if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键 begin if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK') goto lbusetemp --如果表中无主键,则用临时表处理
select @FdName=name from syscolumns where id=@Obj_ID and colid in( select colid from sysindexkeys where @Obj_ID=id and indid in( select indid from sysindexes where @Obj_ID=id and name in( select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID ))) if @@rowcount>1 --检查表中的主键是否为复合主键 begin select @strfd='',@strjoin='',@strwhere='' select @strfd=@strfd+',['+name+']' ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']' ,@strwhere=@strwhere+' and b.['+name+'] is null' from syscolumns where id=@Obj_ID and colid in( select colid from sysindexkeys where @Obj_ID=id and indid in( select indid from sysindexes where @Obj_ID=id and name in( select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID ))) select @strfd=substring(@strfd,2,2000) ,@strjoin=substring(@strjoin,5,4000) ,@strwhere=substring(@strwhere,5,4000) goto lbusepk end end end else goto lbusetemp
//--使用标识列或主键为单一字段的处理方法-- lbuseidentity: exec('select top '+@Id1+@FdShow+' from '+@QueryStr +' where '+@FdName+' not in(select top ' +@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder +')'+@FdOrder ) return
//--表中有复合主键的处理方法-- lbusepk: exec('select '+@FdShow+' from(select top '+@Id1+' a.* from (select top 100 percent * from '+@QueryStr+@FdOrder+') a left join (select top '+@Id2+' '+@strfd+' from '+@QueryStr+@FdOrder+') b on '+@strjoin+' where '+@strwhere+') a' ) return
//--用临时表处理的方法-- lbusetemp: select @FdName='[ID_'+cast(newid() as varchar(40))+']' ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20)) ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+' into #tb from'+@QueryStr+@FdOrder+' select '+@FdShow+' from #tb where '+@FdName+' between ' +@Id1+' and '+@Id2 ) go
好复杂.. 建议用aspnetPager分页控件.. 里面有生成存储过程的..
aspnetPager分页控件.. 能抗1000万的数据么
分页算法1利用Not In和SELECT TOP分页SELECT TOP 10 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 20 id FROM TestTable ORDER BY id)) ORDER BY ID SELECT TOP 页大小 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID (利用Not In和SELECT TOP分页)效率次之,需要拼接SQL语句。
分页算法2(利用ID大于多少和SELECT TOP分页) SELECT TOP 10 * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 20 id FROM TestTable ORDER BY id) AS T)) ORDER BY ID SELECT TOP 页大小 * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id) AS T)) ORDER BY ID (利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句。
利用SQL的游标存储过程分页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(利用SQL的游标存储过程分页)效率最差,但是最为通用。
最好是具体问题具体分析。分页的方法很多 SQL2000的最快的分页语句,但有很大局限性。declare @sqlstr nvarchar(4000),@pagesize int,@pageindex int set @sqlstr='select top '+convert(nvarchar(15),@pagesize)+'* from [tab] where id <(select min(id) from (select top '+convert(nvarchar(15),@pagesize*(@pageindex-1))+' id from [tab] order by id desc)t) order by id desc'
分页算法4 利用 top 分页模型: (1) (2) (3) 100 90 100 99 91 99 98 92 98 97 93 97 96 94 96 95 95 95 94 96 94 . . . . . . . . . 0 100 90 select * from (select top 10 * FROM (select top 页大小*页数 ID,m_sort from Table2 order by m_sort desc)A ORDER BY m_sort ASC)B order by m_sort desc需要拼接SQL,效率比较高
要多个字段排序 字段的值不能保证唯一 date desc,id desc 这个样的
CREATE PROCEDURE GetRecordByPage ( @sTable nvarchar(100), --表名 @sPkey nvarchar(50), --主键(一定要有) @sField nvarchar(1000)='*', --字段 @iPageCurr int, --当前页数 @iPageSize int, --每页记录数 @sCondition nvarchar(1000), --条件(不需要where) @sOrder nvarchar(100) , --排序(不需要order by,需要asc和desc字符) @Counts int=0 output, --记录条数(已有值:外部赋值,0执行count) @pageCount int=1 output --查询结果分页后的总页数 ) AS SET NOCOUNT ON DECLARE @sC1 nvarchar(1000),@sC2 nvarchar(1000) DECLARE @iAsc int,@iDesc int,@iType tinyint DECLARE @sT1 nvarchar(1000),@sT2 nvarchar(1000),@sT3 nvarchar(1000),@sT4 nvarchar(1000),@sSQL nvarchar(4000) /*----------------------判断where 条件是否空值-------------------*/ IF LEN(@sCondition)>2 SELECT @sC1=' WHERE '+@sCondition+' ', @sC2=' WHERE '+@sCondition+' AND ' ELSE BEGIN SELECT @sC1='', @sC2=' WHERE ' END SELECT @sT1=UPPER(@sOrder), @sT2=@sOrder, @iType=0, @sOrder='', @sT4=UPPER(@sPkey) /*-----------------------获取查询的数据行数---------------------*/ IF LEN(@sT2)>2 BEGIN SELECT @iAsc=0, @iDesc=0 IF @sT4=SUBSTRING(@sT1,0,LEN(@sT4)) --存在主建 BEGIN SELECT @iAsc=CHARINDEX('ASC',@sT1), @iDesc=CHARINDEX('DESC',@sT1) END IF (@iAsc>0 and @iDesc=0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc <@iDesc)) SELECT @iType=1, @sT3='>(SELECT MAX(' ELSE IF (@iAsc=0 and @iDesc>0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc>@iDesc)) BEGIN SELECT @iType=1, @sT3=' <(SELECT MIN(' END SET @sOrder=' ORDER BY '+@sT2 END *-------------------------获取查询的数据行数----------------------*/ --IF (@Counts <1) --BEGIN --SET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1 --EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUT --END SET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1 EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUT SET @pageCount=(@Counts+@ipageSize-1)/@ipageSize IF @iPageCurr>@pageCount SET @iPageCurr=@pageCount SELECT @iPageCurr=(CASE WHEN @Counts <(@iPageCurr-1)*@iPageSize THEN CEILING(@Counts/@iPageSize) WHEN @iPageCurr <1 THEN 1 ELSE @iPageCurr END) IF (@iPageCurr>1) AND (@iType=1) SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC2+@sPkey+@sT3+@sPkey+') FROM (SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+') AS tbTemp)'+@sOrder ELSE IF (@iPageCurr>1) AND (@iType=0) SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sC2+@sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC2+@sPkey+' NOT IN(SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+')'+@sOrder+')'+@sOrder ELSE BEGIN SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC1+@sOrder END EXEC(@sSQL) print @sSQL print @Counts print @pageCount SET NOCOUNT OFF GO
// Procedure : spGetPager 分页(来源:程序太平洋网站)
//==============================================================if exists(select [name] from sysobjects where type='p' and [name]='spGetPager')
drop procedure spGetPager
gocreate procedure spGetPager
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end
--如果显示第一页,可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end
--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))
select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理
select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp
//--使用标识列或主键为单一字段的处理方法--
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return
//--表中有复合主键的处理方法--
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return
//--用临时表处理的方法--
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
go
能抗1000万的数据么
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID
(利用Not In和SELECT TOP分页)效率次之,需要拼接SQL语句。
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句。
@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(利用SQL的游标存储过程分页)效率最差,但是最为通用。
SQL2000的最快的分页语句,但有很大局限性。declare @sqlstr nvarchar(4000),@pagesize int,@pageindex int
set @sqlstr='select top '+convert(nvarchar(15),@pagesize)+'* from [tab]
where id <(select min(id) from (select top '+convert(nvarchar(15),@pagesize*(@pageindex-1))+' id from [tab] order by id desc)t) order by id desc'
利用 top 分页模型:
(1) (2) (3)
100 90 100
99 91 99
98 92 98
97 93 97
96 94 96
95 95 95
94 96 94
. . .
. . .
. . .
0 100 90
select *
from
(select top 10 *
FROM (select top 页大小*页数 ID,m_sort
from Table2 order by m_sort desc)A
ORDER BY m_sort ASC)B
order by m_sort desc需要拼接SQL,效率比较高
字段的值不能保证唯一
date desc,id desc
这个样的
(
@sTable nvarchar(100), --表名
@sPkey nvarchar(50), --主键(一定要有)
@sField nvarchar(1000)='*', --字段
@iPageCurr int, --当前页数
@iPageSize int, --每页记录数
@sCondition nvarchar(1000), --条件(不需要where)
@sOrder nvarchar(100) , --排序(不需要order by,需要asc和desc字符)
@Counts int=0 output, --记录条数(已有值:外部赋值,0执行count)
@pageCount int=1 output --查询结果分页后的总页数
)
AS
SET NOCOUNT ON DECLARE @sC1 nvarchar(1000),@sC2 nvarchar(1000)
DECLARE @iAsc int,@iDesc int,@iType tinyint
DECLARE @sT1 nvarchar(1000),@sT2 nvarchar(1000),@sT3 nvarchar(1000),@sT4 nvarchar(1000),@sSQL nvarchar(4000) /*----------------------判断where 条件是否空值-------------------*/
IF LEN(@sCondition)>2
SELECT @sC1=' WHERE '+@sCondition+' ', @sC2=' WHERE '+@sCondition+' AND '
ELSE
BEGIN
SELECT @sC1='', @sC2=' WHERE '
END
SELECT @sT1=UPPER(@sOrder), @sT2=@sOrder, @iType=0, @sOrder='', @sT4=UPPER(@sPkey) /*-----------------------获取查询的数据行数---------------------*/
IF LEN(@sT2)>2
BEGIN
SELECT @iAsc=0, @iDesc=0
IF @sT4=SUBSTRING(@sT1,0,LEN(@sT4)) --存在主建
BEGIN
SELECT @iAsc=CHARINDEX('ASC',@sT1), @iDesc=CHARINDEX('DESC',@sT1)
END IF (@iAsc>0 and @iDesc=0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc <@iDesc))
SELECT @iType=1, @sT3='>(SELECT MAX('
ELSE IF (@iAsc=0 and @iDesc>0) OR ((@iAsc>0 AND @iDesc>0) AND (@iAsc>@iDesc))
BEGIN
SELECT @iType=1, @sT3=' <(SELECT MIN('
END
SET @sOrder=' ORDER BY '+@sT2
END *-------------------------获取查询的数据行数----------------------*/ --IF (@Counts <1)
--BEGIN
--SET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1
--EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUT
--END SET @sSQL='SELECT @Counts=Count(0) FROM '+@sTable+@sC1
EXEC sp_executesql @sSQL,N'@Counts int OUT',@Counts OUT SET @pageCount=(@Counts+@ipageSize-1)/@ipageSize IF @iPageCurr>@pageCount
SET @iPageCurr=@pageCount SELECT @iPageCurr=(CASE WHEN @Counts <(@iPageCurr-1)*@iPageSize THEN CEILING(@Counts/@iPageSize) WHEN @iPageCurr <1 THEN 1 ELSE @iPageCurr END) IF (@iPageCurr>1) AND (@iType=1)
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC2+@sPkey+@sT3+@sPkey+') FROM (SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+') AS tbTemp)'+@sOrder
ELSE IF (@iPageCurr>1) AND (@iType=0)
SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sC2+@sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC2+@sPkey+' NOT IN(SELECT TOP '+CAST((@iPageCurr-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sC1+@sOrder+')'+@sOrder+')'+@sOrder
ELSE
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sC1+@sOrder
END EXEC(@sSQL) print @sSQL
print @Counts
print @pageCount SET NOCOUNT OFF GO
distinct 字段的
我一般用songsu 的第一种not in算法
第二种算法不知道怎么用,应该只能应用于简单的分页需求上而已吧