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