SQL-выбор строк, где одно значение столбца является общим для другого столбца критериев

У меня есть таблица перекрестных ссылок, которая выглядит так:

id  document_id  subject_id
1   8            21
2   5            17
3   5            76
4   7            88
5   9            17
6   9            76
7   2            76

Он соответствует документам для субъектов. Документы могут быть членами более чем одного субъекта. Я хочу вернуть строки из этой таблицы, где данный документ соответствует всем субъектам в заданном наборе. Например, учитывая набор предметов:

(17,76)

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

id  document_id  subject_id
2   5            17
3   5            76
5   9            17
6   9            76

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

Каков самый простой и эффективный способ запроса этого в SQL?

Ответ 1

Я предполагаю, что натурным ключом этой таблицы является document_id + subject_id, и этот идентификатор является суррогатом; IOW, document_id и subject_id уникальны. Таким образом, я просто собираюсь притвориться, что он не существует и что уникальное ограничение находится на естественном ключе.

Начнем с очевидного.

SELECT document_id, subject_id
  FROM document_subjects
 WHERE subject_id IN (17,76)

Это доставит вам все, что вы хотите, плюс то, что вам не нужно. Так что все, что нам нужно сделать, это отфильтровать другие вещи. "Другим материалом" являются группы строк, имеющих счетчик, который не равен счетчику желаемых предметов.

SELECT document_id
  FROM document_subjects
 WHERE subject_id IN (17,76)
 GROUP BY document_id
HAVING COUNT(*) = 2

Обратите внимание, что subject_id удаляется, поскольку он не участвует в группировке. Сделав этот шаг дальше, я собираюсь добавить воображаемую таблицу под названием subject_i_want, которая содержит N строк объектов, которые вы хотите.

SELECT document_id
  FROM document_subjects
 WHERE subject_id IN (SELECT subject_id FROM subjects_i_want)
 GROUP BY document_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM subjects_i_want)

Очевидно, что subject_i_want может быть заменен на другой подзапрос, временную таблицу или что-то еще. Но, как только у вас есть этот список document_id, вы можете использовать его в подзапросе большего запроса.

SELECT document_id, subject_id, ...
  FROM document_subjects
 WHERE document_id IN(
        SELECT document_id
          FROM document_subjects
          WHERE subject_id IN (SELECT subject_id FROM subjects_i_want)
          GROUP BY document_id
         HAVING COUNT(*) = (SELECT COUNT(*) FROM subjects_i_want))

Или что угодно.

Ответ 2

Использование Oracle (или любой базы данных, которая разрешает предложение with). Это позволяет определить значения subject_id ровно один раз.

with t as (select distinct document_id from table1 where subject_id in (17,76) )
select document_id from table1 where subject_id in (select subject_id from t)
group by document_id 
having count(*) = (select count (*) from t);

Ответ 3

Это очень интересный вопрос.

Я предполагаю, что вам нужен более обобщенный запрос, но это то, что я сделал бы в случае, когда у вас всегда будет одинаковое количество предметов (скажем два):

 SELECT T.id, T.document_id, T.subject_id
   FROM table T
        INNER JOIN table T1 ON T.document_id = T1.document_id AND T1.subject_ID = 17
        INNER JOIN table T2 ON T.document_id = T2.document_id AND T2.subject_ID = 76

Конечно, вы можете добавить еще одну INNER JOIN, чтобы добавить еще один идентификатор объекта. Но я признаю, что это не очень хорошее общее решение.

Ответ 4

select document_id from table1
 where subject_id in (17, 76)
 group by document_id
having count(distinct subject_id) = 2