PostgreSQL中如何筛选同时拥有两个指定标签的用户

你原来的查询逻辑存在问题——同一行的title字段不可能同时等于两个不同值，因此需要换思路实现需求。以下是几种可行的解决方案：

方法1：分组统计（通用且高效） #

先关联用户与标签表，过滤出目标标签后按用户分组，通过统计标签数量确保用户同时拥有两个标签。如果用户不会重复添加同一标签，用COUNT(*)即可；若存在重复标签场景，用COUNT(DISTINCT l.title)避免重复计数。

SELECT u.id, STRING_AGG(l.title, ', ') AS titles
FROM users u
JOIN user_labels l ON l.user_id = u.id
WHERE l.title IN ('label1', 'label2')
GROUP BY u.id
HAVING COUNT(DISTINCT l.title) = 2;

如果需要仅保留恰好拥有这两个标签、无其他标签的用户，可调整为：

SELECT u.id, STRING_AGG(l.title, ', ') AS titles
FROM users u
JOIN user_labels l ON l.user_id = u.id
GROUP BY u.id
HAVING COUNT(DISTINCT l.title) = 2
   AND BOOL_AND(l.title IN ('label1', 'label2'));

方法2：使用INTERSECT取交集 #

分别查询拥有label1和label2的用户集合，取交集即可得到同时拥有两个标签的用户：

SELECT u.id
FROM users u
JOIN user_labels l ON l.user_id = u.id
WHERE l.title = 'label1'
INTERSECT
SELECT u.id
FROM users u
JOIN user_labels l ON l.user_id = u.id
WHERE l.title = 'label2';

若需要获取用户的所有标签，可将上述结果与原表关联：

SELECT u.id, STRING_AGG(l.title, ', ') AS titles
FROM users u
JOIN user_labels l ON l.user_id = u.id
WHERE u.id IN (
    SELECT u.id
    FROM users u
    JOIN user_labels l ON l.user_id = u.id
    WHERE l.title = 'label1'
    INTERSECT
    SELECT u.id
    FROM users u
    JOIN user_labels l ON l.user_id = u.id
    WHERE l.title = 'label2'
)
GROUP BY u.id;

方法3：多次JOIN标签表 #

通过两次关联user_labels表，分别匹配label1和label2的记录，直观确保用户同时存在这两个标签：

SELECT u.id, STRING_AGG(DISTINCT l.title, ', ') AS titles
FROM users u
JOIN user_labels l1 ON l1.user_id = u.id AND l1.title = 'label1'
JOIN user_labels l2 ON l2.user_id = u.id AND l2.title = 'label2'
JOIN user_labels l ON l.user_id = u.id
GROUP BY u.id;

该方法逻辑清晰，适合标签数量较少的场景。

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