交流--查询第X页,每页Y条记录

最基本的处理方法:

如果表中有主键(记录不重复的字段也可以),可以用类似下面的方法,当然x,y要换成具体的数字,不能用变量:

select top y * from 表 where 主键 not in(select top (x-1)*y 主键 from 表)



如果表中无主键,可以用临时表,加标识字段解决.这里的x,y可以用变量.

select id=identity(int,1,1),*  into #tb from 表
select * from #tb where id between (x-1)*y and x*y-1

24 个解决方案

#1

--完善一点的,用存储过程实现的通用过程.

/*--用存储过程实现的分页程序

显示指定查询结果的第X页
此存储过程针对无主键的情况,使用临时表的方法
如果有主键,不推荐此方法

--邹建 2003.09--*/

/*--调用示例
exec p_show 'select * from xzkh_new..地区资料'

exec p_show 'select * from xzkh_new..地区资料',5,2,'地区编号,地区名称,助记码','地区编号'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GO

CREATE Proc p_show
@QueryStr nvarchar(1000), --查询语句,如果查询表,用:select * from 表
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(50) --标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
,@FdShow=case isnull(@FdShow,'') when '' then '*' else @FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end

exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from('+@QueryStr+') a '+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO

#2

--上面的只适用于SQL查询,不适用于表,视图(要写成 select * from 表 的形式),下面更完善也更复杂.


/*--用存储过程实现的分页程序

显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法

--邹建 2003.09--*/

/*--调用示例
exec p_show '地区资料'

exec p_show '地区资料',5,3,'地区编号,地区名称,助记码','地区编号'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GO

CREATE Proc p_show
@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

#3

邹建总结了不少好贴子啊，收藏先。

#4

呵呵,这段时间正在整理在CSDN上学到的东西.

如果大家没意见的话,准备将陆续发表一些.

#5

收藏ing！！

#6

辛苦啦

#7

补充说明,对于使用查询的情况,如果查询语句包含order by
就必须使用类似下面的查询,即必须包含top :
select top 100 percent * from 表 order by 字段

#8

请问在oracle下如何实现类似sql server 下 top的功能？？？

#9

oracle我没用过.应该可以吧.

听说ORACLE有个rowid()之类的函数,可以得到行号,可以不用这么麻烦.

#10

程序中的话,最好用ADO的分页功能来实现.

'** ----- 数据库连接字符串模板 ---------------------------------------
'** ACCESS数据库
'** iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
'**     "Password=""密码"";Data Source=数据库名"
'**
'** SQL数据库
'** iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
'**     "User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名"

'VB中的分页例子,以ACCESS数据库为例,SQL数据库只需要更改连接字符串
'引用:Microsoft ActiveX Data Objects 2.x Library
'2.x是版本号
Sub split()
    Dim iRe As ADODB.Recordset
    Dim iConc As String, iCount&, iI&, iJ&
    
    iConc = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
        ";Data Source=F:\My Documents\客户资料.mdb"
    Set iRe = New ADODB.Recordset
    With iRe
        .CursorLocation = adUseClient
        .Open "客户", iConc, adOpenKeyset, adLockOptimistic
        .PageSize = 10
        iCount = .PageCount
        For iI = 1 To iCount
            .AbsolutePage = iI
            For iJ = 1 To .PageSize
                Debug.Print .Fields(1)
                .MoveNext
                If .EOF Then Exit For
            Next
        Next
    End With
    
    iRe.Close
    Set iRe = Nothing
End Sub

#11

'ASP中的分页例子,以SQL数据库为例,ACCESS数据库只需要更改连接字符串
<%
iConc = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名"
Set iRe =Server.CreateObject("ADODB.Recordset")
With iRe
        .CursorLocation = adUseClient
        .Open "表名", iConc, 1,1
        .PageSize = 10        '每页的大小
        iCount = .PageCount   '总页数
        .AbsolutePage = 2     '设置当前显示第几页,这里是第2页
        For iJ = 1 To .PageSize  '循环显示当前页的记录
                '这里改为显示处理的代码
                .MoveNext
                If .EOF Then Exit For
        Next
    End With
    
    iRe.Close
    Set iRe = Nothing
%>

#12

收藏

#13

--实例处理一篇:


--数据测试环境

--检查对象是否存在,如果存在,删除
if exists (select * from dbo.sysobjects where id = object_id(N'[主表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [主表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[从表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [从表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_qry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_qry]
GO

--创建表环境
create table 主表(id varchar(40) not null constraint PK_主表 primary key
,createdate datetime
)

create table 从表(id varchar(40) not null
,indexid varchar(40) not null
,value varchar(500)
)
alter table 从表 add constraint PK_从表 PRIMARY KEY CLUSTERED (id,indexid)

--插入测试数据
insert into 主表
select '01','2003-10-21'
union all select '02','2003-10-21'

insert into 从表
select '01','1001','asdf'
union all select '01','aby6','abcd'
union all select '01','1003','jsfk'
union all select '01','1t0b','yuet'
union all select '02','1001','jdjd'
union all select '02','1002','ksks'
union all select '02','aby6','hyei'

#14

go
--创建分页查询的存储过程
create proc p_qry
@where varchar(8000)='', --查询的条件,属于主表的字段用a.字段,属于从表的字段用b.字段
@pagesize int=20, --每页的大小
@page int=1, --要查询第几页
@createview bit=1 --是否重建视图,第一次调用时或查询条件变化时指定为1,其他情况指定为0
as
declare @sql varchar(8000)
declare @viewname sysname
set @viewname='tmp_qry_'+host_name()+'_'+user_name() --以用户电脑名+登陆的用户名做视图名
if object_id(@viewname) is null
goto lb_createview
else if @createview=1
begin
set @sql='drop view ['+@viewname+']'
exec(@sql)
goto lb_createview
end
goto lb_qry

lb_createview:
if @where<>'' set @where='where ('+@where+')'
exec('create view ['+@viewname+']
as 
select a.*,b.indexid,b.value from 主表 a inner join 从表 b on a.id=b.id
'+@where)

lb_qry:
declare @p1 varchar(20),@p2 varchar(20)
if @page=1
begin
set @p1=cast(@pagesize as varchar)
exec('select top '+@p1+' * from ['+@viewname+']')
end
else
begin
select @p1=cast(@pagesize as varchar)
,@p2=cast((@page-1)*@pagesize as varchar)
exec('select top '+@p1+' * from ['+@viewname+'] a left join
(select top '+@p2+' id,indexid from ['+@viewname+']) b
on a.id=b.id and a.indexid=b.indexid
where b.id is null
')
end
go

#15

--调用测试
exec p_qry @where='',@pagesize=5,@page=2

exec p_qry @where='',@pagesize=5,@page=1,@createview=0  --第二次调用时,因为没有改变查询条件,所以不用再次创建视图.


--带条件的调用
exec p_qry @where='b.indexid=''1002'' and b.value like ''%abcd%'' and a.createdate between ''2003-10-21'' and ''2003-11-21'''
,@pagesize=5,@page=2
exec p_qry @pagesize=5,@page=1,@createview=0   --第二次调用,所以也不用再写条件,只需要设置要调用第几页就行了.

#16

不过,如果表中有主键,而且在程序中的话,还是用ADO分页,或者采用记住本次查询的最大主键/最小主键的方法.



转大力的处理方法

/***  方法一 完全符合题意，可以检索出符合给出指标条件的id的全部属性  **/

---下面开始建立测试环境：

create table t1(id varchar(10),createdate datetime)
insert t1 select '01','2003-10-21'
union all select '02','2003-10-21'

create table t2(id varchar(10),indexid varchar(10),value varchar(10))
insert t2 select '01','1001','asdf'
union all select '01','1002','abcd'
union all select '01','1003','jsfk'
union all select '01','1t0b','yuet'
union all select '02','1001','asdf'
union all select '02','1002','abcd'
union all select '02','1008','hijk'

#17

--建立过程(普通版)
create proc 过程
@页号 int,
@每页大小 int,
@开始时间 varchar(10),
@结束时间 varchar(10),
@指标条件 varchar(8000)
asdeclare @a varchar(8000),@b varchar(8000)
set @a='select identity(int,1,1) mid,a.*,b.indexid,b.value into # from t1 a join t2 b on a.id = b.id where  a.createdate between '''+@开始时间+''' and '''+@结束时间+''' and not exists(select 1 from ('
set @b=') tem where not exists(select 1 from t2 where indexid=tem.indexid and value=tem.value and id=a.id)) select top '+cast(@每页大小 as varchar(10))+' id,createdate,indexid,value from # where mid>('+cast(@页号 as varchar(10))+'-1)*'+cast(@每页大小 as varchar(10))
exec (@a+@指标条件+@b)
go

--调用：

--得到第一页，每页2条记录，开始时间和结束时间自己定义，注意后面的指标条件比较麻烦
exec 过程 2,2,'1900-1-1','2003-10-30', 'select ''1001'' indexid,''asdf'' value union all select ''1003'',''jsfk'''

--测试2:
exec 过程 1,3,'1900-1-1','2003-10-30', 'select ''1001'' indexid,''asdf'' value union all select ''1003'',''jsfk'''

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

---下面是优化输入条件后的(简化输入版)


--drop proc 过程

create proc 过程
@页号 int,
@每页大小 int,
@开始时间 varchar(10),
@结束时间 varchar(10),
@指标条件 varchar(1000)
as
declare @a varchar(8000),@b varchar(8000),@c varchar(8000)

set @c='select '''+replace(replace(@指标条件,';',''' b union all select '''),',',''' a,''')+''''
set @a='select identity(int,1,1) mid,a.*,b.indexid,b.value into # from t1 a join t2 b on a.id = b.id where  a.createdate between '''+@开始时间+''' and '''+@结束时间+''' and not exists(select 1 from ('
set @b=') tem where not exists(select 1 from t2 where indexid=tem.a and value=tem.b and id=a.id)) select top '+cast(@每页大小 as varchar(10))+' id,createdate,indexid,value from # where mid>('+cast(@页号 as varchar(10))+'-1)*'+cast(@每页大小 as varchar(10))
exec (@a+@c+@b)
go

--得到第一页，每页2条记录，开始时间和结束时间自己定义，注意后面的指标条件格式是 , 号分割列 ; 号分割行
exec 过程 1,2,'1900-1-1','2003-10-30', '1001,asdf;1003,jsfk'

--测试2:
exec 过程 2,3,'1900-1-1','2003-10-30', '1001,asdf;1003,jsfk'

-----------------------------------------------------------------------------------------
/*** 

实际使用更改注意：

set @a='select identity(int,1,1) mid,a.*,b.indexid,b.value into # from t1 a join t2 b on a.id = b.id where  a.createdate between '''+@开始时间+''' and '''+@结束时间+''' and not exists(select 1 from ('
中的：
set @a='select identity(int,1,1) mid,a.*,b.indexid,b.value <<====这里楼主务必调整为实际操作的列名，且不要重复列名
比如：
a.列1,a.列2,b.列1 as 别名 <<==这里用了别名来防止重名

如果更改较大，可通过下面方法调试：
过程中：
exec (@a+@c+@b)
暂时改为：
print(@a+@c+@b)
然后再把print出的代码放到另一个查询分析器中检查是否和预先设想一样

有问题或不更改为实际运用中不理解：
可通过短信或[e-mail]
***/

#18

/**  
       优化方案 

它是针对上面的简化输入版优化

**/


--drop proc 过程

create proc 过程
@页号 int,
@每页大小 int,
@开始时间 varchar(10),
@结束时间 varchar(10),
@指标条件 varchar(1000)
as
declare @a varchar(8000),@b varchar(8000),@c varchar(8000)
set @c='select '''+replace(replace(@指标条件,';',''' b union all select '''),',',''' a,''')+''''
set @a='select identity(int,1,1) mid,aa.*,bb.indexid,bb.value into # from (select * from t1 where createdate between '''+@开始时间+''' and '''+@结束时间+''' and not exists(select 1 from ('
set @b=') tem where not exists(select 1 from t2 where indexid=tem.a and value=tem.b and id=t1.id))) aa join t2 bb on aa.id = bb.id select top '+cast(@每页大小 as varchar(10))+' id,createdate,indexid,value from # where mid>('+cast(@页号 as varchar(10))+'-1)*'+cast(@每页大小 as varchar(10))
exec (@a+@c+@b)
go

--得到第一页，每页2条记录，开始时间和结束时间自己定义，注意后面的指标条件格式是 , 号分割列 ; 号分割行
exec 过程 1,2,'1900-1-1','2003-10-30', '1001,asdf;1003,jsfk'

--测试2:
exec 过程 2,3,'1900-1-1','2003-10-30', '1001,asdf;1003,jsfk'

--优化对象是语句，这样性能可以提高。
--你还可以对你的从表的indexid列和value列和id列建立索引,语句如下：
/*
CREATE INDEX 从表_indexid_index ON 从表 (indexid)
CREATE INDEX 从表_value_index ON 从表 (value)
CREATE INDEX 从表_value_index ON 从表 (id)
*/

#19

mark

#20

临时表没有删除阿？
在什么时候删除？
如果不删除会残留在数据库中，使数据库增加负担！

#21

mark

#22

gz

#23

/*
    用存储过程实现的分页程序
*/
CREATE PROC sp_PageRecordset
    @queryStr         nvarchar(1000),       --查询语句, 不要在前面加"SELECT"或"TOP n"
    @keyField         nvarchar (200),       --标识字段
    @pageSize         int,                 --每页的行数
    @pageNumber       int                   --要显示的页码, 从0开始
AS
BEGIN
    DECLARE @sqlText AS nvarchar(4000)
    DECLARE @sqlTable AS nvarchar(4000)
    SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @queryStr
    SET @sqlText =
        'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' + 
        'FROM (' + @sqlTable + ') AS tableA ' +
        'WHERE ' + @keyField + ' NOT IN(SELECT TOP ' +
        CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @keyField + 
        ' FROM (' + @sqlTable + ') AS tableB)'
    EXEC (@sqlText)
END

-----------------------------------------------------------
drop procedure sp_PageRecordset

exec sp_PageRecordset
    @queryStr = ' * from wzTa order by [id]',
    @keyField = '[ID]',
    @pageSize = 100,
    @pageNumber = 6000

#24

收藏