PostgreSQL查询问题：如何筛选同时含指定users_keyword_id的document_id

你的核心需求是找出同时关联users_keyword_id=1和users_keyword_id=2的document_id，再获取该document的对应记录。以下是几种PostgreSQL中简便的实现方式：

方法一：GROUP BY + HAVING 子查询（最通用） #

先通过子查询定位符合条件的document_id，再获取其所有关联记录：

SELECT document_id, users_keyword_id
FROM users_keyword_document
WHERE document_id IN (
    SELECT document_id
    FROM users_keyword_document
    WHERE users_keyword_id IN (1, 2)
    GROUP BY document_id
    HAVING COUNT(DISTINCT users_keyword_id) = 2
);

• 子查询逻辑：筛选出包含目标关键词的记录，按document_id分组后，通过COUNT(DISTINCT...)确保该document同时拥有所有指定的users_keyword_id（这里是2个）。
• 外层查询返回这些document的所有关联行，最终只会得到document_id=65的两条记录。

方法二：窗口函数统计匹配数 #

用窗口函数按document_id分组统计匹配的关键词数量，再筛选符合条件的行：

SELECT document_id, users_keyword_id
FROM (
    SELECT 
        document_id, 
        users_keyword_id,
        COUNT(CASE WHEN users_keyword_id IN (1, 2) THEN 1 END) OVER (PARTITION BY document_id) AS match_count
    FROM users_keyword_document
) t
WHERE match_count = 2;

• 窗口函数OVER (PARTITION BY document_id)会为每个document_id计算其包含目标关键词的数量，最后只保留数量等于指定关键词数的行。

方法三：自连接（适合关键词数量较少的场景） #

通过自连接确保document同时存在两个目标关键词：

SELECT d1.document_id, d1.users_keyword_id
FROM users_keyword_document d1
JOIN users_keyword_document d2 
    ON d1.document_id = d2.document_id
WHERE d1.users_keyword_id IN (1, 2)
  AND d2.users_keyword_id = 1
  AND EXISTS (
      SELECT 1 FROM users_keyword_document d3 
      WHERE d3.document_id = d1.document_id AND d3.users_keyword_id = 2
  );

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