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

[zjcxc]

最基本的处理方法:

如果表中有主键(记录不重复的字段也可以),可以用类似下面的方法,当然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

[prcgolf]

收藏ing！！

[aierong]

辛苦啦

[zjcxc]

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

[linuxyf]

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

[zjcxc]

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

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

[zjcxc]

程序中的话,最好用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

[zjcxc]

'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
%>

[lvltt]

收藏

[zjcxc]

不过,如果表中有主键,而且在程序中的话,还是用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'

[zjcxc]

--建立过程(普通版)
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]
***/

[zjcxc]

/**
优化方案

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

**/


--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)
*/

[Primer2002cn]

mark

[zjcxc]

--实例处理一篇:


--数据测试环境

--检查对象是否存在,如果存在,删除
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'

[zjcxc]

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

[zjcxc]

--调用测试
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 --第二次调用,所以也不用再写条件,只需要设置要调用第几页就行了.

[Roger_long]

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

[shiufurong007]

mark

[jmsofts]

gz

[wjzhou]

/*
用存储过程实现的分页程序
*/
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

[winternet]

收藏