SQL multi-condition combination query, and find all the SQL statements of the minimum subclass according to the specified category.

Copy codeThe Code is as follows:
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)

-- Combination Conditions
DECLARE @ where nvarchar (1000)
SELECT @ WHERE = 'where NOTDISPLAY = 0'

DECLARE ABC CURSOR
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
-- Add the subclass of the value of the specified category to the temporary table
Insert into tbtemclass (ID) select id from tbsdinfoclass where rootid = @ ROOTID

-- Use a cursor to put the minimum category of a specified category into a temporary table
DECLARE CLASSID CURSOR
SELECT ID FROM TBTEMCLASS
OPEN CLASSID
Fetch next from classid into @ ROOTID
WHILE @ FETCH_STATUS = 0
BEGIN
-- If a subclass is determined, The subclass is added to the temporary table and the category is deleted so that the cursor loops in the temporary table.
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

-- Add itself to a temporary table
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

-- Count statement
DECLARE @ countsql nvarchar (500)
SELECT @ COUNTSQL = n' SELECT @ RECORDCOUNT = COUNT (*) from tbsdinfo inner join tbuser on tbsdinfo. USERNAME = TBUSER. username'
SELECT @ COUNTSQL = @ COUNTSQL + @ WHERE
--
-- Execute statistics
EXEC sp_executesql @ COUNTSQL,
N' @ recordcount int out ',
@ RECORDCOUNT OUT
--
-- Calculate the number of pages
SET @ PageCount = CEILING (@ RecordCount * 1.0/@ PageSize)
--
-- Query statement
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

-- Execute Query
-- The query result is to put the records found in the temporary table, and then query the corresponding parent class and root class records through the following cursor
EXEC (@ SQL)

DECLARE @ CLASSID INT
DECLARE @ ID INT

DECLARE TEM CURSOR
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