用于搜索具有无限数量位字段的表的SQL设计方法 [英] SQL design approach for searching a table with an unlimited number of bit fields
问题描述
考虑搜索包含公寓租赁信息的表：使用界面的客户端会选择一些在DB中表示为位字段的条件，例如：
- AllowPets
- HasParking
- HasDeck
- ModernKitchen
etc ..
我们正面临着这样的情况：我们软件的每个新客户端都有额外的他们希望允许最终用户搜索的字段。我有三种方法，我正在考虑，希望输入和/或不同的方法。
- 当前方法：添加更多位字段，SQL查询是动态构建的，并使用EXEC执行：
SET @SQL = @SQL + l。[NumUnits]，'exec（@SQL））
继续添加更多位字段。 （300列的表）
-
在一个字段中将数据表示为一系列位。我不清楚这种方法是否有效，请考虑上面提供的4个采样位字段。这个领域可能看起来像这样：1011这将表明对于'hasparking'是虚假的，但对所有其他人都是真实的。我不清楚的是你如何构建一个查询，你不在乎，如果它是假或真的，例如1？11，搜索需要1,3和4是真的，但不在乎'HasParking'是true或false。
-
移动到一个基于Attribute的方法，你有一个表'AttributeTypeID'和一个表PropertyAttributes，它将PropertyID加到AttributeTypeId，新的位字段只是AttributeTypeID表中的一行。
其他一些方法？这是一个众所周知的SQL设计模式吗？
感谢任何帮助
KM-编辑每个评论
属性表中有一些其他行，称为列表属性
CREATE TABLE [dbo]。[ListingAttributes]（
[清单ID] [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
b $ b SELECT
清单ID，COUNT（AttributeID）AS CountOfMatches
FROM ListingAttributes
WHERE
BoardID = 1
AND
StatusID IN 'A'）
AND
- （select * from @PropertyType）中的--PropertyTypeID
--AND
属性类型IN（2,3,6）
GROUP BY清单ID
HAVING COUNT（AttributeID）=（3）
）
SELECT
count（l.listingid）
FROM列表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标量（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
| --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
| - 过滤器（WHERE：（[Expr1005] =（3）））1 4 3过滤器过滤器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计算标量计算标量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] ListAttributes]。[ListingID]）DEFINE：（[Expr1011] = Count（*）））1 6 5流聚合聚集BY：（。[dbo]。[ListingAttributes]。[ListingID]）[Expr1011] = Count ）9185.126 0 0.01422281 11 0.1875839 [Expr1011] NULL PLAN_ROW 0 1
| --Index Seek（OBJECT：（。[dbo]。[_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索引寻求索引寻求对象：（。[dbo ] [ListAttributes]。[_dta_index_ListingAttributes_BoardID_ListingID__AttributeType_PropertyTypeID_StatusID_6_7]），SEEK：（。[dbo]。[ListingAttributes]。[BoardID] =（1）），WHERE：（。[dbo]。[ListingAttributes]。[StatusID] ='A '[...] [[]] [[AttributeType] =（2）OR。[dbo]。[ListAttributes]。[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行受影响）
这样的东西可能适用于你：
定义表：
CREATE TABLE #Apartments
（
ApartmentID int not null主键标识（1 ，1）
，ApartmentName varchar（500）not null
，状态char（1）not null默认（'A'）
--....
）
CREATE TABLE #AttributeTypes
（
AttributeType smallint not null主键
，AttributeDescription varchar（500）not null
）
CREATE TABLE #Attributes - 布尔属性，如果行存在，则此属性为
（
）公寓ID不为空--FK到Apartments.ApartmentID
，AttributeID int not null主键标识（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值（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） - <<动态更改或拆分CSV字符串并加入
GROUP BY ApartmentID
HAVING COUNT（AttributeID）= 3 - <<动态更改或分割CSV字符串，并从结果表中使用COUNT（*）
）
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公寓名称
----------- --------------
1一
2两
（2 row（s）affected）
在上面的搜索查询中，我刚刚添加了一个CSV字符串的属性ID进行搜索。实际上，您可以创建一个搜索存储过程，您可以在其中传入包含要搜索的ID的CSV参数。您可以查看 这个答案 来了解可以将CSV字符串循环自由拆分成可以加入的表格。这将导致不需要使用任何动态SQL。
编辑基于许多评论：
如果你添加几个列到#AttributeTypes表，你可以动态构建搜索页面。以下是一些建议：
- 状态：ActiveInactive
- ListOrder ：可以使用这个来排序来构建屏幕
- ColumnNumber：可以帮助组织同一个屏幕行上的字段
- AttributeGroupID：分组字段，见下文
- 等。
您可以使所有字段复选框，或添加另一个称为#AttributesGroups的表，并将其组合在一起并使用单选按钮。例如，由于允许携带宠物和无宠物是独占的，请在#AttributesGroups表宠物中添加一行。应用程序将对接口中的属性进行分组。组中的属性将与常规未分组的属性相同，只需收集所选的ID并将其传递到搜索过程。但是，对于每个组，您将需要使应用程序包含无偏好单选按钮并将其默认。此选项将不具有属性ID，并且不会传入，因为您不想考虑属性。
在我的示例中，我将显示一个示例#Apartments
表状态中的超级属性。您只应考虑此表的主要属性。如果您开始使用这些，可能需要将CTE更改为FROM #Apartments，并对这些字段进行过滤，然后连接到#Attributes。不过，您将遇到动态搜索条件的问题，请阅读Erland Sommarskog的这篇文章。
编辑最新评论：
这里是一个代码排除属性列表：
; WITH GetMatchingAttributes AS
（
SELECT
ApartmentID，COUNT （AttributeID）AS CountOfMatches
FROM #Attributes
WHERE AttributeType IN（1,2,3） - <<动态更改或拆分包含CSV字符串并加入
GROUP BY公寓ID
HAVING COUNT（AttributeID）= 3 - <<动态更改或拆分CSV字符串，并从结果中使用COUNT（*）包含表
）
，SomeRemoved AS
（
SELECT
m.ApartmentID
FROM GetMatchingAttributes m
LEFT OUTER JOIN #Attributes a ON m.ApartmentID = a.ApartmentID
AND a.AttributeType IN（5， 6） - < <动态更改或拆分排除CSV字符串并加入
WHERE a.ApartmentID为空
）
SELECT
a。*
FROM #Apartments a
INNER JOIN SomeRemoved m ON a.ApartmentID = m.ApartmentID
WHERE a.Status ='A'
我不认为我会这样走。我会按照我之前在编辑中概述的方法。当包含/排除某个属性时，我只需为每个属性添加允许携带宠物和无宠物。
我从原始帖子更新了示例数据，以显示此信息。
运行原始查询：
- （..， 6，..）找到允许宠物的公寓
- （..，..，7，..）找到不允许宠物的公寓
- （..，..，..）如果没有偏好。
我认为这是更好的方法。当结合上一次编辑中描述的分组思想和动态构建的搜索页面时，我认为这会更好，运行速度更快。
Consider searching a table that contains Apartment Rental Information: A client using the interface selects a number of criteria that are represented as bit fields in the DB, for instance:
- AllowsPets
- HasParking
- HasDeck
- ModernKitchen
etc..
We are facing a situation where each new client of our software has additional fields they want to allow their end users to search on. The number of bit fields could reach into the hundreds.
I have three approaches that I'm considering and hoping for input and/or a different approach.
- Current approach: Add more bit fields, sql queries are built dynamically and executed using EXEC:
SET @SQL = @SQL + 'l.[NumUnits],' exec(@SQL))
Continue to add more bit fields. (table with 300 columns?)
Represent the data as a series of bits in one field. I'm unclear on if this approach will work, consider the 4 sample bit fields I offered above. The field could look like this: 1011 which would indicate false for 'hasparking' but true for all others. What I'm unclear on is how you would structure a query where you didn't care if it was false or true, for instance 1?11 where the person searching needs 1,3 and 4 to be true but doesn't care if 'HasParking' is true or false.
Move to an Attribute based approach where you have a table 'AttributeTypeID' and a table PropertyAttributes, which joins the PropertyID to the AttributeTypeId, new bit fields are simply a row in the AttributeTypeID table.
some other approach? Is this a well known SQL design pattern?
Thanks for any help
KM- EDIT PER COMMENTS
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)
something like this may work for you:
define tables:
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
)
insert sample data:
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
sample search query:
;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)
In the search query above, I just included a CSV string of atribute IDs to search for. In reality, you could create a Search stored procedure where you pass in a CSV parameter containing the IDs to search on. You can look at this answer to learn about loop free splitting of that CSV strings into table which you can join to. This would result in not needing to use any dynamic SQL.
EDIT based on the many comments:
if you add a few columns to the #AttributeTypes table you could dynamically build the search page. Here are a few suggestions:
- Status: "A"ctive "I"nactive
- ListOrder: can use this to sort by to build the screen
- ColumnNumber: can help organize fields on the same screen row
- AttributeGroupID: to group fields, see below
- etc.
You could make all the fields checkboxes, or add another table called #AttributesGroups, and group some together and use radio buttons. For example, since "Pets Allowed" and "No Pets" are exclusive, add a row in the #AttributesGroups table "Pets". The application would group the attributes in the interface. Attributes in Groups would work the same as regular ungrouped attributes, just collect the selected IDs and pass it in to the search procedure. However, for each group you'll need to have the application include a "no preference" radio button and default it on. This option will not have an attribute ID and is not passed in, since you don't want to consider the attribute.
In my example, I do show an example of a "super attribute" that is in the #Apartments table, "Status". You should only consider major attributes for this table. If you start using these, you may want to alter the CTE to be FROM #Apartments with filtering on these fields and then join to #Attributes. However you will run into issues of Dynamic Search Conditions, so read this article by Erland Sommarskog.
EDIT on latest comments:
here is code to have a list of exclude attributes:
;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'
I don't think I would go this way though. I'd go with the approach I outlined in my previous EDIT above. When include/exclude of an attribute is necessary, I'd just add an attribute for each: "Pets allowed" and "No Pets".
I updated the sample data from the original post to show this.
Run the original query with:
- (..,..,6,..) to find apartments that allow pets
- (..,..,7,..) to find apartments where no pets are allowed
- (..,..,..) if there is no preference.
I think this is the better approach. When combined with the grouping idea and dynamically built search page described in the last edit, I think this would be better and would run faster.
这篇关于用于搜索具有无限数量位字段的表的SQL设计方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
