SQL: сопоставление записей по теории множеств

Question

SQL: сопоставление записей по теории множеств

В последнее время я боролся со сложным запросом SQL.
У меня есть следующие таблицы:

[dbo].[User] ~ {ID,nickname}  
[dbo].[Property] ~ {ID,title}  
[dbo].[Property_Values] ~ [ID,propertyID,title}  
[dbo].[Property_Values_User_Linkage] ~ {UserID,PropertyID,valueID}

В основном это проект, в котором пользователь выбирает значения для каждого свойства. Каждое свойство может быть однозначным или многозначным. Например, пользователь может выбрать несколько значений для свойства {ID=1,title=Hobbies}, но должен выбрать одно значение для свойства {ID=2,title=HairColor}.

Используя другую таблицу - [dbo].[Search_Property_Values_User_Linkage] - {UserID,PropertyID,valueID} Я выбираю, какие свойства хочу, и ожидаю найти подходящих пользователей. НО, если я не выбрал значение (или многозначное значение) для, скажем, HairColor, я должен получить всех пользователей (так как я не хочу фильтровать по HairColor).

Пока это просто, но проблема, которую я не могу решить, это случай, когда впереди много значений или нет пользовательских значений. Например, я хочу, чтобы все пользователи с HairColor=Brown и Hobbies IN(баскетбол, футбол).

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

Чтобы поместить код в слова, скажем, мне нужны все пользователи, которые:

Сопоставьте ВСЕ свойство-значение, которое я выбрал
Может иметь другие свойства, такие как EyesColor, но, поскольку я не выбрал значение фильтрации, они также могут быть получены.
Не может быть установлено какое-либо свойство вообще, но так как я не выбрал значение для этого свойства, они ДЕЙСТВИТЕЛЬНЫ!
Сопоставьте все выбранные свойства как группу, а не только одно свойство, которое я выбрал (пользователи, которые любят баскетбол, но имеют "красный" цвет для своего HairColor, недействительны!

Я столкнулся с решением, в котором я создаю виртуальную таблицу, которая "дополняет" невыбранные значения битами. Например (не фактический код):

DECLARE @isMatches bit  
SET @isMatches=0    
if [propertyIsChosen]=1  
{    
 if [userInProperty]=1 SET @isMatches=1   
}  
else SET isMatches=1

Я в основном CROSS-JOIN [Property] WITH [User]
и LEFT-OUTER-JOIN для остальных таблиц, чтобы соответствовать выборки.
Я получаю всех пользователей и их соответствие собственности. Это не достаточно хорошо, так как у меня есть пользователи с каштановыми волосами, те, кто любит баскетбол / футбол, но не те, кто соответствует обоим (и, конечно, любому другому неопределенному свойству от меня).
Это тяжело, но это то, что я получил, чтобы проанализировать проблему.

Буду признателен за любую помощь. Я думаю, что что-то упустил из моих уроков математики, 10 лет назад...

РЕДАКТИРОВАТЬ: db pic: http://i51.tinypic.com/2n1cfwg.png

0

sql linkage cross-join set-theory

Источник

user1009253 23 окт '11 в 06:24

1 ответ

Решение

Другие вопросы по тегам sql linkage cross-join set-theory

user533120 23 окт '11 в 20:15 2011-10-23 20:15 · Accepted Answer · 2011-10-23 20:15

Модель данных

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

DDL SQL (MS SQL Server):

CREATE TABLE [USER] (
    USER_ID int NOT NULL,
    NICKNAME nvarchar(50) NOT NULL,
    CONSTRAINT PK_USER PRIMARY KEY CLUSTERED (USER_ID)
);

CREATE TABLE USER_PROPERTY_VALUE(
    USER_ID int NOT NULL,
    PROPERTY_NAME nvarchar(50) NOT NULL,
    PROPERTY_VALUE_NO int NOT NULL,
    PROPERTY_VALUE nvarchar(255) NOT NULL,
    CONSTRAINT PK_USER_PROPERTY_VALUE PRIMARY KEY CLUSTERED (
        USER_ID,
        PROPERTY_NAME,
        PROPERTY_VALUE_NO
    )
);

ALTER TABLE USER_PROPERTY_VALUE ADD CONSTRAINT FK_USER_PROPERTY_VALUE_USER FOREIGN KEY(USER_ID)
REFERENCES [USER] (USER_ID);

Многозначное значение представлено несколькими строками в USER_PROPERTY_VALUE, в которых используется одно и то же PROPERTY_NAME, но каждая из которых имеет отдельный PROPERTY_VALUE_NO.

Следующий пример данных...

Rob:   HairColor={brown}, Hobby={basketball,football}
Bob:   HairColor={brown}, Hobby={basketball}
Steve: Hobby={basketball,football}

... представлен в базе данных следующим образом:

USER:

USER_ID NICKNAME
1       Rob
2       Bob
3       Steve

USER_PROPERTY_VALUE:

USER_ID PROPERTY_NAME   PROPERTY_VALUE_NO   PROPERTY_VALUE
1       HairColor       1                   brown
1       Hobby           1                   basketball
1       Hobby           2                   football
2       HairColor       1                   brown
2       Hobby           1                   basketball
3       Hobby           1                   basketball
3       Hobby           2                   football

Примеры запросов

Выберите пользователей с коричневым цветом волос:

SELECT *
FROM [USER]
WHERE
    EXISTS (
        SELECT *
        FROM USER_PROPERTY_VALUE 
        WHERE
            USER_PROPERTY_VALUE.USER_ID = [USER].USER_ID
            AND PROPERTY_NAME = 'HairColor'
            AND PROPERTY_VALUE = 'brown'
    )

Результат:

USER_ID NICKNAME
1       Rob
2       Bob

Выберите пользователей, чьи увлечения включают в себя баскетбол и футбол:

SELECT *
FROM [USER]
WHERE
    EXISTS (
        SELECT *
        FROM USER_PROPERTY_VALUE 
        WHERE
            USER_PROPERTY_VALUE.USER_ID = [USER].USER_ID
            AND PROPERTY_NAME = 'Hobby'
            AND PROPERTY_VALUE = 'basketball'
    )
    AND EXISTS (
        SELECT *
        FROM USER_PROPERTY_VALUE 
        WHERE
            USER_PROPERTY_VALUE.USER_ID = [USER].USER_ID
            AND PROPERTY_NAME = 'Hobby'
            AND PROPERTY_VALUE = 'football'
    )

Результат:

USER_ID NICKNAME
1       Rob
3       Steve

Выберите пользователей, чей цвет волос - коричневый, а хобби - баскетбол и футбол.

SELECT *
FROM [USER]
WHERE
    EXISTS (
        SELECT *
        FROM USER_PROPERTY_VALUE 
        WHERE
            USER_PROPERTY_VALUE.USER_ID = [USER].USER_ID
            AND PROPERTY_NAME = 'HairColor'
            AND PROPERTY_VALUE = 'brown'
    )
    AND EXISTS (
        SELECT *
        FROM USER_PROPERTY_VALUE 
        WHERE
            USER_PROPERTY_VALUE.USER_ID = [USER].USER_ID
            AND PROPERTY_NAME = 'Hobby'
            AND PROPERTY_VALUE = 'basketball'
    )
    AND EXISTS (
        SELECT *
        FROM USER_PROPERTY_VALUE 
        WHERE
            USER_PROPERTY_VALUE.USER_ID = [USER].USER_ID
            AND PROPERTY_NAME = 'Hobby'
            AND PROPERTY_VALUE = 'football'
    )

Результат:

USER_ID NICKNAME
1       Rob

И т. Д., И т. Д.

Запросы на основе содержимого другой таблицы

Допустим, у вас есть таблица, которая содержит критерии фильтра:

CREATE TABLE PROPERTY_FILTER (
    PROPERTY_NAME nvarchar(50) NOT NULL,
    PROPERTY_VALUE nvarchar(255) NOT NULL,
    CONSTRAINT PK_PROPERTY_FILTER PRIMARY KEY (PROPERTY_NAME, PROPERTY_VALUE)
)

Следующий запрос вернет только пользователей, которые соответствуют критериям, которые в настоящее время содержатся в этой таблице:

SELECT *
FROM [USER] U
WHERE
    NOT EXISTS (
        SELECT F.PROPERTY_NAME, F.PROPERTY_VALUE
        FROM PROPERTY_FILTER F
        EXCEPT
        SELECT P.PROPERTY_NAME, P.PROPERTY_VALUE
        FROM USER_PROPERTY_VALUE P
        WHERE P.USER_ID = U.USER_ID
    )

Говоря простым языком: если есть свойство фильтра, которое также не является свойством пользователя, игнорируйте этого пользователя.

Кстати, это не будет работать в параллельной многопользовательской среде - вам нужно будет ввести дополнительное поле в таблице PROPERTY_FILTER, чтобы определить "сеанс", или использовать временные таблицы (если вам не требуется постоянство).