SQL Server sql 多条件组合查询,并根据指定类别找出所有最小子类别的SQL语句备忘
2022-11-12 09:46:09
内容摘要
这篇文章主要为大家详细介绍了SQL Server sql 多条件组合查询,并根据指定类别找出所有最小子类别的SQL语句备忘,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看
文章正文
这篇文章主要为大家详细介绍了SQL Server sql 多条件组合查询,并根据指定类别找出所有最小子类别的SQL语句备忘,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 | <code>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 </code> |
注:关于SQL Server sql 多条件组合查询,并根据指定类别找出所有最小子类别的SQL语句备忘的内容就先介绍到这里,更多相关文章的可以留意
代码注释