考虑搜索包含“公寓出租信息”的表：使用该接口的客户端选择许多标准，这些标准在DB中表示为位字段，例如：
等等..
我们面临的情况是，我们软件的每个新客户端都有其他字段，希望它们允许最终用户进行搜索。位字段的数量可能达到数百个。
我正在考虑三种方法，希望能提供输入和/或其他方法。
SET @SQL = @SQL + 'l.[NumUnits],' exec(@SQL))
继续添加更多的位字段。（具有300列的表格？）
将数据表示为一个字段中的一系列位。我尚不清楚这种方法是否可行，请考虑我上面提供的4个样本位字段。该字段可能看起来像这样：1011，对于“乱码”将指示为false，但对于所有其他字段，将为true。我不清楚的是，您将如何构造一个查询，不管它是假还是真，例如1？11，其中搜索的人需要1、3和4为真，但不在乎“已停车”是对还是错。
转到基于属性的方法，其中有一个表’AttributeTypeID’和一个表PropertyAttributes，该表将PropertyID连接到AttributeTypeId，新位字段只是AttributeTypeID表中的一行。
其他方法？这是众所周知的SQL设计模式吗？
谢谢你的帮助
KM-每评论编辑
attribute table has a few other rows in it and is called listingattributes CREATE TABLE [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, PRIMARY KEY CLUSTERED ( [AttributeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] ;WITH GetMatchingAttributes AS ( SELECT ListingID,COUNT(AttributeID) AS CountOfMatches FROM ListingAttributes WHERE BoardID = 1 AND StatusID IN ('A') AND --PropertyTypeID in (select * from @PropertyType) --AND AttributeType IN (2,3,6) GROUP BY ListingID HAVING COUNT(AttributeID)=(3) ) SELECT count(l.listingid) FROM Listing l INNER JOIN GetMatchingAttributes m ON l.ListingID=m.ListingID -- where -- StatusID IN (select * from @Status) --AND --PropertyTypeID in (select * from @PropertyType) 1 1 0 NULL NULL 1 NULL 1 NULL NULL NULL 0.1934759 NULL NULL SELECT 0 NULL |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1012],0))) 1 2 1 Compute Scalar Compute Scalar 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 |--Stream Aggregate(DEFINE:([Expr1012]=Count(*))) 1 3 2 Stream Aggregate Aggregate NULL [Expr1012]=Count(*) 1 0 0.001483165 11 0.1934759 [Expr1012] NULL PLAN_ROW 0 1 |--Filter(WHERE:([Expr1005]=(3))) 1 4 3 Filter Filter WHERE:([Expr1005]=(3)) NULL 2471.109 0 0.00440886 9 0.1919928 NULL NULL PLAN_ROW 0 1 |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0))) 1 5 4 Compute Scalar Compute Scalar 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 |--Stream Aggregate(GROUP BY:(.[dbo].[ListingAttributes].[ListingID]) DEFINE:([Expr1011]=Count(*))) 1 6 5 Stream Aggregate Aggregate GROUP BY:(.[dbo].[ListingAttributes].[ListingID]) [Expr1011]=Count(*) 9185.126 0 0.01422281 11 0.1875839 [Expr1011] NULL PLAN_ROW 0 1 |--Index Seek(OBJECT:(.[dbo].[ListingAttributes].[_dta_index_ListingAttributes_BoardID_ListingID__AttributeType_PropertyTypeID_StatusID_6_7]), SEEK:(.[dbo].[ListingAttributes].[BoardID]=(1)), WHERE:(.[dbo].[ListingAttributes].[StatusID]='A' AND (.[dbo].[ListingAttributes].[AttributeType]=(2) OR .[dbo].[ListingAttributes].[AttributeType]=(3) OR .[dbo].[ListingAttributes].[AttributeType]=(6))) ORDERED FORWARD) 1 7 6 Index Seek Index Seek OBJECT:(.[dbo].[ListingAttributes].[_dta_index_ListingAttributes_BoardID_ListingID__AttributeType_PropertyTypeID_StatusID_6_7]), SEEK:(.[dbo].[ListingAttributes].[BoardID]=(1)), WHERE:(.[dbo].[ListingAttributes].[StatusID]='A' AND (.[dbo].[ListingAttributes].[AttributeType]=(2) OR .[dbo].[ListingAttributes].[AttributeType]=(3) OR .[dbo].[ListingAttributes].[AttributeType]=(6))) ORDERED FORWARD .[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 row(s) affected)
这样的事情可能为您工作：
定义表：
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参数。
*根据许多评论进行 *编辑 ：
如果您在#AttributeTypes表中添加几列，则可以动态构建搜索页面。这里有一些建议：
您可以选中所有字段复选框，或添加另一个名为#AttributesGroups的表，然后将其中一些分组并使用单选按钮。例如，由于“允许的宠物”和“禁止携带的宠物”是互斥的，因此请在#AttributesGroups表“宠物”中添加一行。应用程序会将接口中的属性分组。组中的属性与常规未分组属性的工作原理相同，只是收集选定的ID并将其传递给搜索过程。但是，对于每个组，您都需要使该应用程序包括一个“无首选项”单选按钮并将其默认设置为打开。由于您不想考虑该属性，因此该选项将没有属性ID，也不会传递。
在我的示例中，我确实显示了#Apartments表“状态”中“超级属性”的示例。您应该只考虑该表的主要属性。如果您开始使用这些属性，则可能希望将CTE更改为FROM
search-2005.html)问题，因此请阅读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中概述的方法。当需要包含/排除属性时，我只需为每个属性添加一个属性：“允许携带宠物”和“禁止携带宠物”。
我更新了原始帖子中的示例数据以显示此信息。
使用以下命令运行原始查询：
我认为这是更好的方法。当结合上一次编辑中描述的分组思想和动态构建的搜索页面时，我认为这会更好，并且运行速度会更快。
