SQL 选择其中一列值在另一条件列中通用的行

Question

我有一个交叉引用表，如下所示:

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 中查询这个的最简单和最有效的方法是什么？

Answer 1

我假设这个表的自然键是 document_id + subject_id，而那个 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 被删除，因为它不参与分组。更进一步，我将添加一个名为subjects_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)

显然，subjects_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))

管他呢。