sql 多条件组合查询，并根据指定类别找出所有最小子类别的SQL语句备忘

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