我有一个像这样的mysql表:
id_doc | id_category
1 a
1 c
1 f
2 a
2 g
3 a
3 b
3 f
4 h
我需要一个查询来提取具有类别“a”和“F”的文档。
示例:
id_doc
1
3
请帮帮我!谢谢
聚合并使用having
检查文档所在的不同组数是否等于2。
SELECT id_doc
FROM document_category
WHERE id_category IN ('a',
'f')
GROUP BY id_doc
HAVING count(DISTINCT id_category) = 2;
或者,如果您有一个documents表,则使用两个exists
。
SELECT d.id_doc
FROM document d
WHERE EXISTS (SELECT *
FROM document_category dc
WHERE dc.id_doc = d.id_doc
AND dc.id_category = 'a')
AND EXISTS (SELECT *
FROM document_category dc
WHERE dc.id_doc = d.id_doc
AND dc.id_category = 'f');