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