Sql выбирает записи с соответствующими подмножествами

Есть два набора сотрудников: менеджеры и ворчание.
Для каждого менеджера есть таблица manager_meetings, которая содержит список встреч, на которых присутствовал каждый менеджер. Аналогичная таблица grunt_meetings содержит список встреч, на которых присутствовал каждый хрюканье.

Итак:

manager_meetings                          grunt_meetings
managerID      meetingID                  gruntID        meetingID
1              a                          4              a
1              b                          4              b
1              c                          4              c
2              a                          4              d
2              b                          5              a 
3              c                          5              b
3              d                          5              c
3              e                          6              a 
                                          6              c 
                                          7              b 
                                          7              a

Владельцу это не нравится, когда менеджер и ворчание знают точно такую же информацию. У него болит голова. Он хочет идентифицировать эту ситуацию, поэтому он может понизить репутацию менеджера до ворчания или рекламировать менеджера, или взять их в гольф. Владелец любит играть в гольф.

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

Ожидаемые результаты:

ManagerID            GruntID
2                    7
1                    5

... потому что менеджер 2 и grunt 7 оба присутствовали (a, b), в то время как менеджер 1 и ворчать 5 присутствовали (a, b, c).

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

Есть ли лучший способ сделать "WHERE {subset1} = {subset2}"? Похоже, я пропустил какой-то умный вид.

SQL Fiddle

Ответ 1

Вот версия, которая работает:

select m.mId, g.gId, count(*) --select m.mid, g.gid, mm.meetingid, gm.meetingid as gmm
from manager m cross join
     grunt g left outer join
     (select mm.*, count(*) over (partition by mm.mid) as cnt
      from manager_meeting mm
     ) mm
     on mm.mid = m.mId full outer join
     (select gm.*, count(*) over (partition by gm.gid) as cnt
      from grunt_meeting gm
     ) gm
     on gm.gid = g.gid and gm.meetingid = mm.meetingid 
group by m.mId, g.gId, mm.cnt, gm.cnt
having count(*) = mm.cnt and mm.cnt = gm.cnt;

Метод сравнения строк короче, возможно, легче понять и, вероятно, быстрее.

EDIT:

Для вашего конкретного случая получения точных совпадений запрос можно упростить:

select mm.mId, gm.gId
from (select mm.*, count(*) over (partition by mm.mid) as cnt
      from manager_meeting mm
     ) mm join
     (select gm.*, count(*) over (partition by gm.gid) as cnt
      from grunt_meeting gm
     ) gm
     on gm.meetingid = mm.meetingid and
        mm.cnt = gm.cnt
group by mm.mId, gm.gId
having count(*) = max(mm.cnt);

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

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

Ответ 2

Альтернативная версия - но требует другой таблицы. В принципе, мы даем каждой встрече отличную силу из двух, поскольку она "ценит", а затем суммирует каждую стоимость встречи менеджера и значение каждого хрюкающего собрания. Если они одинаковы, у нас есть матч.

Должно быть возможно сделать таблицу meeting_values TVF, но это немного проще.

SQL Fiddle

Дополнительная таблица:

CREATE TABLE meeting_values (value INT, meetingID CHAR(1));
INSERT INTO meeting_values VALUES
 (1,'a'),(2,'b'),(4,'c'),(8,'d'),(16,'e');

И запрос:

SELECT managemeets.mID, gruntmeets.gID
FROM
    ( SELECT gm.gID, sum(value) AS meeting_totals
      FROM grunt_meeting gm 
             INNER JOIN 
       meeting_values mv ON gm.meetingID = mv.meetingID
      GROUP BY gm.gID 
    ) gruntmeets 
     INNER JOIN
    ( SELECT mm.mID, sum(value) AS meeting_totals
      FROM manager_meeting mm 
             INNER JOIN 
           meeting_values mv ON mm.meetingID = mv.meetingID
      GROUP BY mm.mID 
    ) managemeets ON gruntmeets.meeting_totals = managemeets.meeting_totals

Ответ 3

Попытка отомстить поражение Аарона - решение используя EXCEPT:

SELECT
  m.mID,
  g.gID
FROM
  manager AS m
INNER JOIN
  grunt AS g
ON  NOT EXISTS (
  SELECT meetingID
  FROM   manager_meeting
  WHERE  mID = m.mID
  EXCEPT
  SELECT meetingID
  FROM   grunt_meeting
  WHERE  gID = g.gID
)
AND NOT EXISTS (
  SELECT meetingID
  FROM   grunt_meeting
  WHERE  gID = g.gID
  EXCEPT
  SELECT meetingID
  FROM   manager_meeting
  WHERE  mID = m.mID
);

В принципе, вычтите изрядный набор собраний из набора встреч менеджеров, затем наоборот. Если ни один результат не содержит строк, хрюканье и менеджер присутствовали на одном и том же собрании.

Обратите внимание, что этот запрос будет соответствовать менеджерам и ворчаниям, которые никогда не посещали ни одну встречу.