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

摘要：复制代码代码如下:DECLARE@PAGESIZEINTDECLARE@PAGEINDEXINTDECLARE@PAGECOUNTINTDE...

复制代码代码如下:

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