编写SQL查询：获取同时拥有多个指定document_type_id的archive_id

数据示例 #

+------------+------------------+
| archive_id | document_type_id |
+------------+------------------+
|          1 |               20 |
|          1 |               18 |
|          3 |               20 |
|          4 |               11 |
|          2 |               23 |
|          5 |               20 |
|          6 |               23 |
|          6 |               20 |
|          6 |               18 |
+------------+------------------+

需求1：获取同时包含document_type_id=18和20的archive_id #

预期结果：

+------------+
| archive_id |
+------------+
|          1 |
|          6 |
+------------+

方法1：GROUP BY + HAVING（推荐，扩展性强）

SELECT archive_id
FROM your_table_name
WHERE document_type_id IN (18, 20)
GROUP BY archive_id
HAVING COUNT(DISTINCT document_type_id) = 2;

• 逻辑：先过滤出目标类型的记录，按archive_id分组后，筛选分组内包含所有指定类型的条目（这里需要2种不同类型，所以计数等于2）。
• 注意：如果表中不存在(archive_id, document_type_id)的重复记录，可以去掉DISTINCT，直接用COUNT(*) = 2，提升查询效率。

方法2：自连接查询

SELECT DISTINCT t1.archive_id
FROM your_table_name t1
JOIN your_table_name t2 ON t1.archive_id = t2.archive_id
WHERE t1.document_type_id = 18
  AND t2.document_type_id = 20;

• 逻辑：通过自连接匹配同时存在18和20类型的archive_id，用DISTINCT避免重复结果。

需求2：获取同时包含document_type_id=18、20和23的archive_id #

预期结果：

+------------+
| archive_id |
+------------+
|          6 |
+------------+

方法1：GROUP BY + HAVING

SELECT archive_id
FROM your_table_name
WHERE document_type_id IN (18, 20, 23)
GROUP BY archive_id
HAVING COUNT(DISTINCT document_type_id) = 3;

• 逻辑和需求1一致，仅需调整IN条件的数量和HAVING的计数。

方法2：三次自连接

SELECT DISTINCT t1.archive_id
FROM your_table_name t1
JOIN your_table_name t2 ON t1.archive_id = t2.archive_id
JOIN your_table_name t3 ON t1.archive_id = t3.archive_id
WHERE t1.document_type_id = 18
  AND t2.document_type_id = 20
  AND t3.document_type_id = 23;

内容的提问来源于stack exchange，提问作者Steph74