Как определить, существует ли группа данных в таблице, учитывая данные, которые должны появиться в строках группы?

Question

Я записываю данные в таблицу и выделяю "идентификатор группы" для каждой записываемой партии данных. Для иллюстрации рассмотрим следующую таблицу.

GroupId  Value
-------  -----
      1      a
      1      b
      1      c
      2      a
      2      b
      3      a
      3      b
      3      c
      3      d

В этом примере есть три группы данных, каждая из которых имеет похожие, но разные значения.

Как я могу запросить эту таблицу, чтобы найти группу, которая содержит данный набор значений? Например, если я запрашиваю (a,b,c), результат должен быть группой 1. Аналогично, запрос для (b,a) должен приводить к группе 2, и запрос для (a, b, c, e) должен привести к пустому набору.

Я могу написать хранимую процедуру, которая выполняет следующие шаги:

• выберите отдельный GroupId из групп и сохраните его локально
• для каждого отдельного GroupId: выполнить разность наборов (except) между входными и табличными значениями (для группы) и наоборот
• вернуть GroupId, если обе операции с разностями множеств дали пустые множества

Это кажется немного чрезмерным, и я надеюсь использовать некоторые другие команды в SQL для упрощения. Есть ли более простой способ выполнить сравнение наборов в этом контексте или выбрать идентификатор группы, который содержит точные входные значения для запроса?

Answer 1

Это запрос внутри набора. Мне нравится решать это с помощью group by а также having:

select groupid
from GroupValues gv
group by groupid
having sum(case when value = 'a' then 1 else 0 end) > 0 and
       sum(case when value = 'b' then 1 else 0 end) > 0 and
       sum(case when value = 'c' then 1 else 0 end) > 0 and
       sum(case when value not in ('a', 'b', 'c') then 1 else - end) = 0;

Первые три условия в having условие проверки, что каждый элемент существует. Последнее условие проверяет, что других значений нет. Этот метод довольно гибкий, для различных исключений и условий включения для значений, которые вы ищете.

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

Если вы хотите передать список, вы можете использовать:

with thelist as (
      select 'a' as value union all
      select 'b' union all
      select 'c'
     )
select groupid
from GroupValues gv left outer join
     thelist
     on gv.value = thelist.value
group by groupid
having count(distinct gv.value) = (select count(*) from thelist) and
       count(distinct (case when gv.value = thelist.value then gv.value end)) = count(distinct gv.value);

Здесь having Предложение подсчитывает количество совпадающих значений и гарантирует, что это тот же размер, что и список.

РЕДАКТИРОВАТЬ: ошибка компиляции запроса из-за отсутствия псевдонима таблицы. обновлено с правильным псевдонимом таблицы.

Answer 2

Это некрасиво, но это работает. На больших наборах данных я не уверен, как будет выглядеть производительность, но вложенные экземпляры #GroupValues выключить GroupID в основной таблице, так что я думаю, пока у вас есть хороший индекс на GroupID это вероятно не было бы слишком ужасно.

If      Object_ID('tempdb..#GroupValues') Is Not Null Drop Table #GroupValues
Create  Table #GroupValues (GroupID Int, Val Varchar(10));
Insert  #GroupValues (GroupID, Val)
Values  (1,'a'),(1,'b'),(1,'c'),(2,'a'),(2,'b'),(3,'a'),(3,'b'),(3,'c'),(3,'d');

If      Object_ID('tempdb..#FindValues') Is Not Null Drop Table #FindValues
Create  Table #FindValues (Val Varchar(10));
Insert  #FindValues (Val)
Values  ('a'),('b'),('c');

Select  Distinct gv.GroupID
From   (Select  Distinct GroupID 
        From    #GroupValues) gv
Where   Not Exists (Select  1
                    From    #FindValues fv2
                    Where   Not Exists (Select  1
                                        From    #GroupValues gv2
                                        Where   gv.GroupID = gv2.GroupID
                                        And     fv2.Val = gv2.Val))
And     Not Exists (Select  1
                    From    #GroupValues gv3
                    Where   gv3.GroupID = gv.GroupID
                    And     Not Exists (Select  1
                                        From    #FindValues fv3
                                        Where   gv3.Val = fv3.Val))