用于搜刮具有无穷数目的位字段的表的SQL计划要领

思量搜刮包括公寓租赁信息的表：行使该界面的客户端选择在数据库中暗示为位字段的多个前提,譬喻：

> 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)

办理要领

（编辑：湖南网）

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