Поиск соответствующего списка элементов в SQL

У меня есть таблица и связанный список атрибутов в другой таблице. Это выглядит так:

Таблица:

ID | State | ... ---------------- 1 | 10 | ... 2 | 15 | ... 3 | 10 | ... 

(Состояние здесь не имеет смысла, я просто добавил его, чтобы показать, что эта таблица содержит дополнительные столбцы)

Табличные фигуры :

 ID | Unit_ID (FK) | Shape ------------------------- 1 | 1 | 40 2 | 1 | 40 3 | 1 | 45 4 | 3 | 10 

Таким образом, каждая строка в Unit может иметь различные связанные формы. Порядок фигур не важен, но форма может быть связана несколько раз.

В моем примере У Units есть следующие Формы:

  • Unit 1 : 40, 40, 45
  • Unit 2 : Нет
  • Unit 3 : 10

Все идет нормально. Это отлично работает при извлечении / обновлении полного набора фигур для данного Unit ID.

Но у меня есть требование выбрать все Units которые имеют определенный список связанных Shapes . Например, я хочу найти все Units которые имеют Shapes 40, 40, 45 (должны возвращать Unit_ID 1).

Я уже нашел несколько похожих вопросов и статей, которые используют реляционное деление, но я не уверен, будет ли это работать, поскольку большинство решений не могут обрабатывать значения, которые появляются не один раз. Я хочу получить точное совпадение, если я ищу 40, 45 Я хочу, чтобы запрос не возвращал никаких результатов в мой пример, так как нет Unit с точно подобными Shapes (мне понадобится запрос для обработки этого случая, выберите Units с Shapes которые содержат подмножество – но это, вероятно, будет легко, как только я знаю, как искать точное совпадение).

Я использую SQL Server 2012.

Следующий подход начинается с помещения списка в таблицу (ну, CTE) с каждой формой и количество раз. Затем он выполняет различные проверки.

Во-первых, каждая фигура в shapes появляется правильное количество раз. Отсюда и происходит внутреннее агрегирование.

Затем он подсчитывает количество фигур, которые соответствуют и подтверждают, что это общее количество различных фигур в tofind :

 with tofind as ( select 40 as shape, 2 as cnt union all select 45 as shape, 1 as cnt ) select s.unit_id from (select s.unit_id, tofind.cnt as tf_cnt, count(s.id) as s_cnt from shapes s join tofind on s.shape = tofind.shape group by s.unit_id, tofind.shape, tofind.cnt ) s group by s.unit_id having sum(case when s_cnt = tf_cnt then 1 else 0 end) = (select count(*) from tofind); 

РЕДАКТИРОВАТЬ:

Вот скрипт SQL, демонстрирующий, что он работает. Однако приведенный выше код не ищет точное совпадение, поскольку в записи могут быть другие фигуры. Следующая модификация работает только для точного соответствия:

 with tofind as ( select 40 as shape, 2 as cnt union all select 45 as shape, 1 as cnt ) select s.unit_id from (select s.unit_id, tofind.cnt as tf_cnt, count(s.id) as s_cnt from shapes s left outer join tofind on s.shape = tofind.shape group by s.unit_id, tofind.shape, tofind.cnt ) s group by s.unit_id having sum(case when s_cnt = tf_cnt then 1 else 0 end) = (select count(*) from tofind) and count(*) = sum(case when s_cnt = tf_cnt then 1 else 0 end); 

Разница заключается в left outer join и дополнительном условии в условии having .

Interesting Posts

EntityType не имеет определенного ключа

SQL Получить последние уникальные строки

Соединения с файлами SQL Server (* .mdf) требуют, чтобы SQL Server Express 2005 функционировал должным образом

Статус задания SQL

Последовательные номера, случайно выбранные и добавленные в таблицу

SQL Server: COALESCE вызывает чрезмерное время выполнения

Получить максимальную длину каждого столбца динамически SQL

Развертывание проекта базы данных Visual Studio 2010

Текущая дата со временем окончания в sql

Возможно ли иметь таблицу с переменными столбцами?

Получение только источника и несоответствие данных с помощью утилиты tablediff

Производительность запросов nhibernate

Существуют ли инструменты визуализации для реализации иерархического стиля данных SQL-запросов в качестве результата вывода?

Разбирайте значение nvarchar, которое выглядит как упрощенный XML

Как получить сумму продаж в месяц?

Давайте будем гением компьютера.