Postgres中无需array_agg，如何根据用户ID集合精准匹配对应团队

要实现给定用户ID集合与团队的精准匹配（每个团队的用户集合唯一），可以采用以下几种高效且无需array_agg的写法：

方法一：基于计数与存在性校验 #

核心思路是验证团队的用户总数等于目标集合大小，同时确保团队所有用户都在目标集合内，且目标集合的每个用户都属于该团队。

假设目标用户ID集合为{1,2}，查询语句如下：

SELECT team
FROM teams
GROUP BY team
HAVING COUNT(DISTINCT user_id) = 2
   AND NOT EXISTS (
       SELECT 1 FROM teams t2 
       WHERE t2.team = teams.team 
       AND t2.user_id NOT IN (1,2)
   )
   AND (SELECT COUNT(DISTINCT user_id) FROM teams t2 WHERE t2.team = teams.team AND t2.user_id IN (1,2)) = 2;

方法二：利用临时表关联校验 #

通过将目标用户集合转换为临时表，关联团队表后做双重校验：匹配的用户数等于目标总数，且团队无额外用户。

WITH target_users AS (
    SELECT unnest('{1,2}'::int[]) AS user_id
)
SELECT t.team
FROM teams t
JOIN target_users tu ON t.user_id = tu.user_id
GROUP BY t.team
HAVING COUNT(DISTINCT t.user_id) = (SELECT COUNT(*) FROM target_users)
   AND NOT EXISTS (
       SELECT 1 FROM teams t2
       WHERE t2.team = t.team
       AND t2.user_id NOT IN (SELECT user_id FROM target_users)
   );

性能优化提示 #

为了提升查询效率，建议给teams表创建复合索引：

CREATE INDEX idx_team_user ON teams(team, user_id);

该索引能大幅加速分组、关联和存在性校验的查询过程。

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