复制代码 代码如下: 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
复制代码 代码如下: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
