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

发布时间：2020-12-12 06:12:55 所属栏目：MsSql教程来源：网络整理

导读：考虑搜索包含公寓租赁信息的表：使用该界面的客户端选择在数据库中表示为位字段的多个条件,例如： AllowPets HasParking HasDeck ModernKitchen 等等.. 我们面临的情况是,我们软件的每个新客户都有他们希望允许最终用户搜索的其他字段.位字段的数量可以达到数

考虑搜索包含公寓租赁信息的表：使用该界面的客户端选择在数据库中表示为位字段的多个条件,例如：

> AllowPets
> HasParking
> HasDeck
> ModernKitchen

等等..

我们面临的情况是,我们软件的每个新客户都有他们希望允许最终用户搜索的其他字段.位字段的数量可以达到数百个.

我有三种方法,我正在考虑并希望输入和/或不同的方法.

>当前方法：添加更多位字段,动态构建SQL查询并使用EXEC执行：SET @SQL = @SQL’l.[NumUnits],’exec(@SQL))

继续添加更多位字段. (300列的表？)

>将数据表示为一个字段中的一系列位.我不清楚这种方法是否有效,考虑我上面提供的4个样本位字段.该字段可能如下所示：1011表示“hasparking”为false,但对所有其他字段为true.我不清楚你是如何构建一个你不关心它是否为真或假的查询,例如1？11,搜索者需要1,3和4才是真的但不关心’HasParking’是真还是假.
>转到基于属性的方法,其中有一个表’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,0))                                                                                                                                                                                                                                                                                                                                                                                                                     [Expr1006]=CONVERT_IMPLICIT(int,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,0))                                                                                                                                                                                                                                                                                                                                                                                                                     [Expr1005]=CONVERT_IMPLICIT(int,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]),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].[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,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,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)

在上面的搜索查询中,我只是包含了要搜索的属性ID的CSV字符串.实际上,您可以创建一个搜索存储过程,在其中传入包含要搜索的ID的CSV参数.您可以查看this answer以了解可以将CSV字符串无循环拆分到可以加入的表中.这将导致不需要使用任何动态SQL.

编辑基于许多评论：

如果向#AttributeTypes表添加几列,则可以动态构建搜索页面.以下是一些建议：

>状态：“A”ctive“I”nactive
> ListOrder：可以使用它来排序以构建屏幕
> ColumnNumber：可以帮助组织同一屏幕行上的字段
> AttributeGroupID：分组字段,见下文
>等

您可以创建所有字段复选框,或添加另一个名为#AttributesGroups的表,并将其组合在一起并使用单选按钮.例如,由于“Pets Allowed”和“No Pets”是独占的,因此在#AttributesGroups表“Pets”中添加一行.应用程序将在界面中对属性进行分组.组中的属性与常规的未组合属性相同,只需收集选定的ID并将其传递给搜索过程.但是,对于每个组,您需要让应用程序包含“无首选项”单选按钮并将其默认打开.此选项没有属性ID,因为您不想考虑该属性,所以不会传入该选项.

在我的示例中,我确实展示了#Apartments中的“超级属性”的示例
表,“状态”.您应该只考虑此表的主要属性.如果您开始使用这些,您可能希望将CTE更改为FROM #Apartments并对这些字段进行过滤,然后加入#Attributes.但是你会遇到Dynamic Search Conditions,so read this article by Erland Sommarskog的问题.

编辑最新评论：

这里是代码,有一个排除属性列表：

;WITH GetMatchingAttributes AS
(
SELECT
    ApartmentID,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'

我不认为我会这样做.我会按照我之前编辑的方法进行操作.当需要包含/排除属性时,我只为每个属性添加一个属性：“允许携带宠物”和“禁止携带宠物”.

我更新了原始帖子中的示例数据以显示此信息.

运行原始查询：

>(..,..,6,..)找到允许携带宠物的公寓
>(..,7,..)找到不允许携带宠物的公寓
>(..,..)如果没有偏好.

我认为这是更好的方法.当与上一次编辑中描述的分组构思和动态构建的搜索页面结合使用时,我认为这会更好并且运行得更快.

（编辑：李大同）

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