用于搜索具有无限数量的位字段的表的SQL设计方法

插入样本数据：

SET NO COUNT ON
INSERT INTO #Apartments VALUES ('one','A')
INSERT INTO #Apartments VALUES ('two','A')
INSERT INTO #Apartments VALUES ('three','I')
INSERT INTO #Apartments VALUES ('four','I')

INSERT INTO #AttributeTypes VALUES (1,'dishwasher')
INSERT INTO #AttributeTypes VALUES (2,'deck')
INSERT INTO #AttributeTypes VALUES (3,'pool')
INSERT INTO #AttributeTypes VALUES (4,'pets allowed')
INSERT INTO #AttributeTypes VALUES (5,'washer/dryer')
INSERT INTO #AttributeTypes VALUES (6,'Pets Alowed')
INSERT INTO #AttributeTypes VALUES (7,'No Pets')

INSERT INTO #Attributes (ApartmentID,AttributeType) VALUES (1,1)
INSERT INTO #Attributes (ApartmentID,2)
INSERT INTO #Attributes (ApartmentID,3)
INSERT INTO #Attributes (ApartmentID,4)
INSERT INTO #Attributes (ApartmentID,5)
INSERT INTO #Attributes (ApartmentID,6)

INSERT INTO #Attributes (ApartmentID,AttributeType) VALUES (2,7)

INSERT INTO #Attributes (ApartmentID,AttributeType) VALUES (3,4)

INSERT INTO #Attributes (ApartmentID,AttributeType) VALUES (4,2)
SET NOCOUNT OFF

样本搜索查询：

;WITH GetMatchingAttributes AS
(
SELECT
    ApartmentID,COUNT(AttributeID) AS CountOfMatches
    FROM #Attributes
    WHERE AttributeType IN (1,2,3)  --<<change dynamically or split a CSV string and join in
    GROUP BY ApartmentID
    HAVING COUNT(AttributeID)=3--<<change dynamically or split a CSV string and use COUNT(*) from resulting table
)
SELECT
    a.*
    FROM #Apartments                      a
        INNER JOIN GetMatchingAttributes m ON a.ApartmentID=m.ApartmentID
    WHERE a.Status='A'
    ORDER BY m.CountOfMatches DESC

OUTPUT：

ApartmentID ApartmentName 
----------- --------------
1           one           
2           two           

(2 row(s) affected)

（编辑：网站开发网_安阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!