欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-10-20 23:53:40
复制代码 代码如下:declare @pagesize int declare @pageindex int declare @pagecount int declare...
复制代码 代码如下:

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