考虑搜索包含“公寓出租信息”的表：使用该界面的客户端选择许多标准，这些标准表示为DB中的位字段，例如：
允许宠物
有停车位
HasDeck
现代厨房
等等..
我们面临着这样一种情况，我们的软件的每个新客户端都具有希望允许其最终用户进行搜索的其他字段。位字段的数量可能达到数百个。
我正在考虑三种方法，希望能提供输入和/或其他方法。
当前方法：添加更多位字段，使用EXEC动态构建并执行sql查询：SET @SQL = @SQL + 'l.[NumUnits],' exec(@SQL))
继续添加更多位字段。 （具有300列的表格？）
将数据表示为一个字段中的一系列位。我不清楚这种方法是否可行，请考虑我上面提供的4个样本位字段。该字段可能看起来像这样：1011，对于“乱码”将指示为false，但对于所有其他字段，将为true。我不清楚的是，您将如何构造一个查询，不管它是假还是真，例如1？11，其中搜索的人需要1,3和4为真，但不在乎“已停车”是对还是错。
转到基于属性的方法，其中有一个表'AttributeTypeID'和一个表PropertyAttributes，该表将PropertyID连接到AttributeTypeId，新位字段只是AttributeTypeID表中的一行。
其他方法？这是众所周知的SQL设计模式吗？
谢谢你的帮助
KM-每评论编辑
属性表中还有其他几行，被称为listingattributes
创建表[dbo]。[ListingAttributes]（
[ListingID] [bigint] NOT NULL，
[AttributeID] [int] IDENTITY（1,1）NOT NULL，
[AttributeType] [smallint] NOT NULL，
[BoardID] [int] NOT NULL，
[ListingMLS] [varchar]（30）NOT NULL，
[PropertyTypeID] [char]（3）NOT NULL，
[StatusID] [varchar]（2）NOT NULL，
主键集群
（
[AttributeID] ASC
）与（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON，FILLFACTOR = 80）一起启用[主]
）在[PRIMARY]上
;带有GetMatchingAttributes AS
（
选择
ListingID，COUNT（AttributeID）AS CountOfMatches
FROM ListingAttributes
哪里
BoardID = 1
和
StatusID IN（'A'）
和
--PropertyTypeID（从@PropertyType中选择*）
- 和
AttributeType IN（2,3,6）
GROUP BY ListingID
拥有COUNT（AttributeID）=（3）
）
选择
计数（l.listingid）
从清单l
内联接GetMatchingAttributes m ON l.ListingID = m.ListingID
-在哪里
-StatusID IN（从@Status中选择*）
- 和
--PropertyTypeID in（从@PropertyType中选择*）1 1 0 NULL NULL 1 NULL 1 NULL NULL NULL 0.1934759 NULL空选择0空
|-计算标量（DEFINE：（[[Expr1006] = CONVERT_IMPLICIT（int，[Expr1012]，0）））1 2 1计算标量计算标量DEFINE：（[[Expr1006] = CONVERT_IMPLICIT（int，[Expr1012]，0））[Expr1006] = CONVERT_IMPLICIT（int，[Expr1012]，0）1 0 0.001483165 11 0.1934759 [Expr1006] NULL PLAN_ROW 0 1
|-流聚合（DEFINE：（[[Expr1012] = Count（*）））1 3 2流聚合聚合NULL [Expr1012] =计数（*）1 0 0.001483165 11 0.1934759 [Expr1012] NULL PLAN_ROW 0 1
| --Filter（WHERE：（[[Expr1005] =（3））））1 4 3过滤器Filter WHERE：（[Expr1005] =（3））空2471.109 0 0.00440886 9 0.1919928空NULL PLAN_ROW 0 1
|-计算标量（DEFINE：（[[Expr1005] = CONVERT_IMPLICIT（int，[Expr1011]，0）））1 5 4计算标量计算标量DEFINE：（[[Expr1005] = CONVERT_IMPLICIT（int，[Expr1011]，0））[Expr1005] = CONVERT_IMPLICIT（int，[Expr1011]，0）9185.126 0 0.01422281 11 0.1875839 [Expr1005] NULL PLAN_ROW 0 1
|-流聚合（GROUP BY：（.. [dbo]。[ListingAttributes]。[ListingID]）DEFINE：（[[Expr1011] = Count（*）））1 6 5流聚合集合GROUP BY :( .. dbo 。[ListingAttributes]。[ListingID]） [Expr1011] =计数（*）9185.126 0 0.01422281 11 0.1875839 [Expr1011]空PLAN_ROW 0 1
|-索引搜索（OBJECT：（。[dbo]。[ListingAttributes]。[_ dta_index_ListingAttributes_BoardID_ListingID__AttributeType_PropertyTypeID_StatusID_6_7]））SEEK：（。[dbo]。[ListingAttributes]。[BoardID] =（1）），WHERE :(。 ]。[ListingAttributes]。[StatusID] ='A'AND（。[dbo]。[ListingAttributes]。[AttributeType] =（2）OR。[dbo]。[ListingAttributes]。[AttributeType] =（3）OR。 [dbo]。[ListingAttributes .. [AttributeType] =（6）））向前排序）1 7 6索引寻求索引寻求对象：（。[dbo]。[ListingAttributes]。[_ dta_index_ListingAttributes_BoardID_ListingID__AttributeType_PropertyTypeID_StatusID_6 _（（7）。 ]。[ListingAttributes]。[BoardID] =（1）），在这里：（。[dbo]。[ListingAttributes]。[StatusID] ='A'AND（。[dbo]。[ListingAttributes]。[AttributeType] =（ 2）OR。[dbo]。[ListingAttributes]。[AttributeType] =（3）OR。[dbo]。[ListingAttributes]。[Attr ibuteType] =（6）））向前订购。[dbo]。[ListingAttributes]。[ListingID]，。[dbo]。[ListingAttributes]。[AttributeID]，。[dbo]。[ListingAttributes]。[AttributeType]，。 [dbo]。[ListingAttributes]。[StatusID] 16050.41 0.09677318 0.0315279 26 0.1283011。[dbo]。[ListingAttributes]。[ListingID]，。[dbo]。[ListingAttributes]。[AttributeID]，。[dbo]。[ListingAttributes] 。[AttributeType]，。[dbo]。[ListingAttributes]。[StatusID] NULL PLAN_ROW 0 1
（受影响的7行）
最佳答案
这样的事情可能为您工作：
定义表：
CREATE TABLE #Apartments
(
ApartmentID int not null primary key identity(1,1)
,ApartmentName varchar(500) not null
,Status char(1) not null default ('A')
--....
)
CREATE TABLE #AttributeTypes
(
AttributeType smallint not null primary key
,AttributeDescription varchar(500) not null
)
CREATE TABLE #Attributes --boolean attributes, if row exists apartment has this attribute
(
ApartmentID int not null --FK to Apartments.ApartmentID
,AttributeID int not null primary key identity(1,1)
,AttributeType smallint not null --fk to AttributeTypes
)
插入样本数据：
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, AttributeType) VALUES (1,2)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (1,3)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (1,4)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (1,5)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (1,6)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (2,1)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (2,2)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (2,3)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (2,4)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (2,7)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (3,1)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (3,2)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (3,3)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (3,4)
INSERT INTO #Attributes (ApartmentID, AttributeType) VALUES (4,1)
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
输出：
ApartmentID ApartmentName
----------- --------------
1 one
2 two
(2 row(s) affected)
在上面的搜索查询中，我只包含了要搜索的带有属性ID的CSV字符串。实际上，您可以创建一个Search存储过程，在其中传递包含要搜索的ID的CSV参数。您可以查看this answer，以了解将CSV字符串无循环拆分到可以加入的表中。这将导致不需要使用任何动态SQL。
根据许多评论进行编辑：
如果您在#AttributeTypes表中添加几列，则可以动态构建搜索页面。这里有一些建议：
状态：“ A”活动“ I”活动
ListOrder：可以使用它来排序以构建屏幕
ColumnNumber：可以帮助组织同一屏幕行上的字段
AttributeGroupID：将字段分组，请参见下文
等等
您可以选中所有字段，或添加另一个名为#AttributesGroups的表，然后将其中一些分组并使用单选按钮。例如，由于“允许的宠物”和“禁止携带的宠物”是互斥的，因此请在#AttributesGroups表“宠物”中添加一行。应用程序会将接口中的属性分组。组中的属性将与常规未分组的属性相同，只是收集选定的ID并将其传递给搜索过程。但是，对于每个组，您都需要使该应用程序包括一个“无首选项”单选按钮并将其默认设置为打开。该选项将没有属性ID，并且不会传递，因为您不想考虑该属性。
在我的示例中，我确实显示了#Apartments中的“超级属性”示例
表“状态”。您应该只考虑该表的主要属性。如果您开始使用这些属性，则可能希望将CTE更改为FROM #Apartments，并在这些字段上进行过滤，然后加入#Attributes。但是，您会遇到Dynamic Search Conditions, so read this article by Erland Sommarskog的问题。
编辑最新评论：
这是具有排除属性列表的代码：
;WITH GetMatchingAttributes AS
(
SELECT
ApartmentID,COUNT(AttributeID) AS CountOfMatches
FROM #Attributes
WHERE AttributeType IN (1,2,3) --<<change dynamically or split an include CSV string and join in
GROUP BY ApartmentID
HAVING COUNT(AttributeID)=3--<<change dynamically or split a CSV string and use COUNT(*) from resulting include table
)
, SomeRemoved AS
(
SELECT
m.ApartmentID
FROM GetMatchingAttributes m
LEFT OUTER JOIN #Attributes a ON m.ApartmentID=a.ApartmentID
AND a.AttributeType IN (5,6) --<<change dynamically or split an exclude CSV string and join in
WHERE a.ApartmentID IS NULL
)
SELECT
a.*
FROM #Apartments a
INNER JOIN SomeRemoved m ON a.ApartmentID=m.ApartmentID
WHERE a.Status='A'
我不认为我会这样走。我将采用上面我先前的EDIT中概述的方法。当需要包含/排除属性时，我只需为每个属性添加一个属性：“允许携带宠物”和“禁止携带宠物”。
我更新了原始帖子中的示例数据以显示此信息。
使用以下命令运行原始查询：
（..，..，6，..）寻找可养宠物的公寓
（..，..，7，..）查找不允许带宠物的公寓
（..，..，..）如果没有偏好。
我认为这是更好的方法。当结合上一次编辑中描述的分组思想和动态构建的搜索页面时，我认为这会更好，并且运行速度会更快。