复制代码 代码如下:
declare @pagesize int
declare @pageindex int
declare @pagecount int
declare @recordcount int
select @pagesize=5
select @pageindex=1
declare @fieldname varchar(50)
declare @fieldvalue varchar(50)
declare @operation varchar(50)
–组合条件
declare @where nvarchar(1000)
select @where=’ where notdisplay=0 ‘
declare abc cursor for
select fieldname,fieldvalue,operation from tbparameters
open abc
fetch next from abc into @fieldname,@fieldvalue,@operation
while @@fetch_status=0
begin
if(@operation = ‘like’)
select @where=@where + ‘ and ‘ + @fieldname + ‘ like ”%’+@fieldvalue+’%”’
else
begin
if(@fieldname=’classid’)
begin
declare @rootid int
select @rootid=@fieldvalue
–将指定类别的值的子类加入临时表
insert into tbtemclass(id) select id from tbsdinfoclass where rootid=@rootid
–使用游标来将指定类别的最小类别提出放入临时表
declare classid cursor for
select id from tbtemclass
open classid
fetch next from classid into @rootid
while @@fetch_status=0
begin
–如果判断有子类则将子类加入临时表，并删除该类别，以使游标在临时表中循环
if(exists(select id from tbsdinfoclass where rootid=@rootid))
begin
insert into tbtemclass(id) select id from tbsdinfoclass where rootid=@rootid
delete from tbtemclass where id=@rootid
end
fetch next from classid into @rootid
end
close classid
deallocate classid
–将自身加入临时表
insert into tbtemclass(id) select @fieldvalue
select @where=@where +’ and classid in(select id from tbtemclass)’
end
else
select @where=@where + ‘ and ‘ + @fieldname + @operation+@fieldvalue
end
fetch next from abc into @fieldname,@fieldvalue,@operation
end
close abc
deallocate abc
truncate table tbparameters
— –计数语句
declare @countsql nvarchar(500)
select @countsql=n’select @recordcount=count(*) from tbsdinfo inner join tbuser on tbsdinfo.username=tbuser.username ‘
select @countsql=@countsql+@where
—
— –执行统计
exec sp_executesql @countsql,
n’@recordcount int out’,
@recordcount out
—
— –计算页数
set @pagecount = ceiling(@recordcount * 1.0 / @pagesize)
—
— –查询语句
declare @sql nvarchar(2000)
declare @orderby varchar(100)
select @orderby=’ order by tbsdinfo.iscommon desc,tbsdinfo.commontime desc,tbsdinfo.createtime desc’
if(@pageindex=1)
begin
select @sql=’insert into tbteminfo(id,title,remark,createtime,endtime,webdomain,classid,classname,typeid,typename,provinceid,province,cityid,city,companyname,address,usertype) ‘
select @sql=@sql+’select top ‘+convert(varchar(4),@pagesize)+’ tbsdinfo.id,title,remark,tbsdinfo.createtime,endtime, webdomain,tbsdinfo.classid,(select classname from tbsdinfoclass where tbsdinfoclass.id=tbsdinfo.classid) as classname,typeid,(select typename from tbsdinfotype where tbsdinfo.typeid=tbsdinfotype.id) as typename,provinceid,(select province from tbprovince where tbprovince.id=provinceid) as province,cityid,(select city from tbcity where tbcity.id=cityid) as city,companyname,tbsdinfo.address,usertype from tbsdinfo inner join tbuser on tbuser.username=tbsdinfo.username’
select @sql=@sql+@where
select @sql=@sql+@orderby
end
else
begin
declare @minrecord int
select @minrecord=(@pageindex-1)*@pagesize
select @sql=’insert into tbteminfo(id,title,remark,createtime,endtime,webdomain,classid,classname,typeid,typename,provinceid,province,cityid,city,companyname,address,usertype) ‘
select @sql=@sql+’select top ‘+convert(varchar(4),@pagesize)+’ tbsdinfo.id, title,remark,tbsdinfo.createtime,endtime, webdomain,tbsdinfo.classid,(select classname from tbsdinfoclass where tbsdinfoclass.id=tbsdinfo.classid) as classname,typeid,(select typename from tbsdinfotype where tbsdinfo.typeid=tbsdinfotype.id) as typename,provinceid,(select province from tbprovince where tbprovince.id=provinceid) as province,cityid,(select city from tbcity where tbcity.id=cityid) as city,companyname,tbsdinfo.address,usertype from tbsdinfo inner join tbuser on tbuser.username=tbsdinfo.username’
if(@where<>”)
select @sql=@sql+@where+’ and ‘
else
select @sql=@sql+’ where ‘
select @sql=@sql+’ tbsdinfo.id not in(select top ‘+convert(varchar(4),@minrecord)+’ tbsdinfo.id from tbsdinfo inner join tbuser on tbuser.username=tbsdinfo.username ‘+@where+@orderby+’)’
select @sql=@sql+@orderby
end
–print @sql
–执行查询
–查询的结果是将找到的记录放入临时表，再通过以下游标查询出相应的父类与根类记录
exec (@sql)
declare @classid int
declare @id int
declare tem cursor for
select id,classid from tbteminfo
open tem
fetch next from tem into @id,@classid
while @@fetch_status=0
begin
declare @ns varchar(500)
declare @ds varchar(200)
select @ns=”
select @ds=”
declare @temrootid int
declare @temts varchar(50)
select @classid=id,@temts=classname,@temrootid=rootid from tbsdinfoclass where id=@classid
select @ns=@temts+’#’+@ns
select @ds=convert(varchar(10),@classid)+’#’+@ds
while(@temrootid>0)
begin
select @temrootid=rootid,@classid=id,@temts=classname from tbsdinfoclass where id=@temrootid
select @ns=@temts+’#’+@ns
select @ds=convert(varchar(10),@classid)+’#’+@ds
end
update tbteminfo set ns=@ns,ds=@ds where id=@id
fetch next from tem into @id,@classid
end
close tem
deallocate tem
select * from tbteminfo
truncate table tbteminfo
truncate table tbtemclass