Дизайн SQL приближается для поиска таблицы с неограниченным количеством битовых полей

что-то это может сработать для вас:

определить таблицы:

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)

В поисковом запросе выше я просто включил строку CSV с идентификаторами атрибутов искать. На самом деле, вы можете создать хранимую процедуру поиска, в которой вы передадите параметр CSV, содержащий идентификаторы для поиска. Вы можете посмотреть этот ответ , чтобы узнать о разбиении без цикла этих строк CSV в таблицу, к которой вы можете присоединиться. Это приведет к тому, что не потребуется использовать какой-либо динамический SQL.

ИЗМЕНИТЬ на основе многих комментариев:

Если вы добавите несколько столбцов в таблицу #AttributeTypes, вы сможете динамически построить страницу поиска. Вот несколько предложений:

• Статус: "Активный" I "nactive
• ListOrder: можно использовать это для сортировки по построению экрана
• ColumnNumber: может помочь организовать поля в одной строке экрана
• AttributeGroupID: для группировки полей, см. Ниже
• и т. Д.

Вы можете установить все поля флажками или добавить еще одну таблицу с именем #AttributesGroups, сгруппировать некоторые вместе и использовать переключатели. Например, поскольку «Домашние животные разрешены» и «Нет домашних животных» являются исключительными, добавьте строку в таблицу «Домашние животные» #AttributesGroups. Приложение сгруппирует атрибуты в интерфейсе. Атрибуты в группах будут работать так же, как обычные несгруппированные атрибуты, просто соберите выбранные идентификаторы и передайте их процедуре поиска. Однако для каждой группы вам необходимо, чтобы приложение включало переключатель «без предпочтений» и по умолчанию. У этой опции не будет идентификатора атрибута, и она не передается, поскольку вы не хотите рассматривать атрибут.

В моем примере я показываю пример «суператрибут», который находится в #Apartments таблица "Статус". Вы должны учитывать только основные атрибуты этой таблицы. Если вы начнете использовать их, вы можете изменить CTE на FROM #Apartments с фильтрацией по этим полям, а затем присоединиться к #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'

Я не думаю, что пошел бы по этому пути. Я бы пошел с подходом, изложенным в моем предыдущем РЕДАКТИРОВАНИИ выше. Когда необходимо включить / исключить атрибут, я бы просто добавил атрибут для каждого: «Разрешены домашние животные» и «Нет домашних животных».

Я обновил образцы данных из исходного сообщения, чтобы показать это.

Выполните исходный запрос с помощью:

• (.., .., 6, .. ), чтобы найти квартиры, в которых разрешено проживание с домашними животными
• (.., .., 7, ..), чтобы найти квартиры, в которых не разрешено проживание с домашними животными
• (.., .., ..), если нет предпочтений.

Я думаю, что это лучший подход. В сочетании с идеей группировки и динамически созданной страницей поиска, описанной в последней редакции, я думаю, что это было бы лучше и работало бы быстрее.