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

这篇文章主要为大家详细介绍了SQL Server sql 多条件组合查询，并根据指定类别找出所有最小子类别的SQL语句备忘，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

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

注：关于SQL Server sql 多条件组合查询，并根据指定类别找出所有最小子类别的SQL语句备忘的内容就先介绍到这里，更多相关文章的可以留意