１５００分求解：主从表查询并分页，UP无分,原1000分贴因描述不清而结贴。

Question

哈哈，原来如此：Select identity(int,1,1) as iid,a.*,b.indexid,b.value into #tmp from 主表 a join 从表 b on a.id = b.id
where  b.indexid = '1002' and b.value = 'abcd'
     and b.id in (Select id from 从表 where indexid = '1008' and value = 'hijk') 
     and a.createdate between '2003-10-21' and '2003-11-10' )
declare @page int,@pagecount int
set @page = 3,@pagecount = 20   -- 显示第三页，每页20笔资料
Select * from #tmp where iid between (@page-1) * pagecount+1 and @page * pagecount

Answer 1

测试：
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','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','abcd'
union all select '02','1008','hijk'Select identity(int,1,1) as iid,a.*,b.indexid,b.value 
into #tmp from t1 a join t2 b on a.id = b.id
where  b.indexid = '1002' and b.value = 'abcd'
     and b.id in (Select id from t2 where indexid = '1008' and value = 'hijk') 
     and a.createdate between '2003-10-21' and '2003-11-10' 
declare @page int,@pagecount int
set @page = 1 set @pagecount = 20   -- 显示第三页，每页20笔资料
Select * from #tmp where iid between (@page-1) * @pagecount+1 and @page * @pagecountiid         id         createdate                                             indexid    value      
----------- ---------- ------------------------------------------------------ ---------- ---------- 
1           02         2003-10-21 00:00:00.000                                1002       abcd（所影响的行数为 1 行）
--drop table t1,t2,#tmp

Answer 2

--数据测试环境--检查对象是否存在,如果存在,删除
if exists (select * from dbo.sysobjects where id = object_id(N'[主表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [主表]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[从表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [从表]
GOif 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'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_qrylb_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--调用测试
exec p_qry @where='',@pagesize=5,@page=2
exec p_qry @where='',@pagesize=5,@page=2
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

Answer 3

--注意在多次调用时的参数设置问题:--第一次查询,查询第一页
exec p_qry @where=''b.indexid=''1002''',@pagesize=5,@page=2--再接着查询第3页
exec p_qry @pagesize=5,@page=3,@createview=0 --直接利用上次创建的视图--再查询第2页
exec p_qry @pagesize=5,@page=2,@createview=0 --直接利用上次创建的视图--再查询第7页
exec p_qry @pagesize=5,@page=7,@createview=0 --直接利用上次创建的视图

Answer 4

/***  方法一 完全符合题意，可以检索出符合给出指标条件的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'
-------------------------------------------------------------------------
--建立过程(普通版)
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出的代码放到另一个查询分析器中检查是否和预先设想一样有问题或不更改为实际运用中不理解：
可通过短信或[email protected]
***/

Answer 5

/**  
       优化方案 它是针对上面的简化输入版优化**/
--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)
*/

Answer 6

use lh_test
go
create view dbo.test
as select (select count(*) from 从表 b2 where (b2.id+b2.indexid) <=(b.id+b.indexid))
as cnt,a.*,b.indexid,b.value 
from 主表 a,从表 b
where a.id=b.id 
go
create CLUSTERED index i_cnt_test on dbo.test (cnt)  --在这里建立集簇索引，起到order by b.id,b.indexid的作用
go
create index i_icreatedate_test on dbo.test (createdate)
go
create index i_indexid_test on dbo.test (indexid,value)
goCREATE PROCEDURE GetProductsPaged
   @page_size int, @date1 datetime,date2 datetime,
@indexid1 varchar(?),@value1 varchar(?)
AS
SET ROWCOUNT @page_size
SELECT * FROM test where 
 createdate between @date1 and @date2 and 
and
(
   (indexid=@indexid1 and value=@value1)
 )
go 
--注：必须满足b.id为char且有一定格式，如果不是的话，建议使用convert(char(?),格式化id),另：借用了大力的几十个字，关键原因是因为我实在太懒。另：未经验证，但觉得带索引的视图是一个尝试方向。
--第一页：(每页100条)
exec GetProductsPaged 100,'2003-10-21','2003-11-10','1001','1547'
--第二页：(在前台取得上一比最后一条记录得id和indexid)

Answer 7

如果楼主的数据进行排序，再查询的话，就可以利用在前台记录上次查询的最大：id indexid 及最小：id indexid 
从而在进行上一页，下一面的查询时，直接从记录的开始点/结束点进行再查询，提高查询速度

Answer 8

更正两个错误
1、在创建view 的时候，加上WITH SCHEMABINDING 选项
2、第一个视图索引据说必须是唯一 唯一聚集
即： 
create view dbo.test
WITH SCHEMABINDING 
as select (select count(*) from 从表 b2 where (b2.id+b2.indexid) <=(b.id+b.indexid))as cnt,
    a.*,b.indexid,b.value 
from 主表 a,从表 b
where a.id=b.id 
go
create UNIQUE CLUSTERED index i_cnt_test on dbo.test (cnt)  --在这里建立集簇索引，起到order by b.id,b.indexid的作用
go
create index i_icreatedate_test on dbo.test (createdate)
go
create index i_indexid_test on dbo.test (indexid,value)
goCREATE PROCEDURE GetProductsPaged
   @page_size int, @date1 datetime,date2 datetime,
@indexid1 varchar(?),@value1 varchar(?)
AS
SET ROWCOUNT @page_size
SELECT * FROM test where 
 createdate between @date1 and @date2 and 
and
(
   (indexid=@indexid1 and value=@value1)
 )
go 
--注：必须满足b.id为char且有一定格式，如果不是的话，建议使用convert(char(?),格式化id),另：借用了大力的几十个字，关键原因是因为我实在太懒。另：未经验证，但觉得带索引的视图是一个尝试方向。
--第一页：(每页100条)
exec GetProductsPaged 100,'2003-10-21','2003-11-10','1001','1547'
--第二页：(在前台取得上一比最后一条记录得id和indexid)

Answer 9

再次更改建立视图语句,以杜绝varchar可能带来的问题。
create view dbo.test
WITH SCHEMABINDING 
as select (select count(*) from &acute;&Oacute;±í b2 where (convert(char(40),b2.id)+convert(char(?),b2.indexid)) <=(convert(char(40),b.id)+convert(char(?),b.indexid)) )as cnt,
    a.*,b.indexid,b.value 
from 主表 a,从表 b
where a.id=b.id 

Answer 10

--修改存储过程，使它可以根据表中的字段，自动创建相应的视图--创建分页查询的存储过程
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_qrylb_createview:
if @where<>'' set @where='where ('+@where+')'
set @sql=''
select @sql=@sql+',a.['+a.name+']' 
from(
select name from syscolumns where object_id('主表')=id
) a left join (
select name from syscolumns where object_id('从表')=id
) b on a.name=b.name
where b.name is null
set @sql=substring(@sql,2,8000)
exec('create view ['+@viewname+']
as 
select '+@sql+',b.* 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

Answer 11

也就只能这样了
create view dbo.test
WITH SCHEMABINDING 
as select    a.id,a.indexid,b.indexid,b.value 
from 主表 a,从表 b
where a.id=b.id 
go
create UNIQUE CLUSTERED  i_indexid_test on dbo.test (indexid,value)  --在这里建立集簇索引，起到order by b.id,b.indexid的作用
go
create index i_icreatedate_test on dbo.test (createdate)
go
CREATE PROCEDURE GetProductsPaged
   @page_size int, @date1 datetime,date2 datetime,
@indexid1 varchar(?),@value1 varchar(?)
AS
SET ROWCOUNT @page_size
SELECT * FROM test where 
 createdate between @date1 and @date2 and 
and
(
   (indexid=@indexid1 and value=@value1)
 )
go 
--注：必须满足b.id为char且有一定格式，如果不是的话，建议使用convert(char(?),格式化id),另：借用了大力的几十个字，关键原因是因为我实在太懒。另：未经验证，但觉得带索引的视图是一个尝试方向。
--第一页：(每页100条)
exec GetProductsPaged 100,'2003-10-21','2003-11-10','1001','1547'
--第二页：(在前台取得上一比最后一条记录得id和indexid)