SQL:查找包含列表用户的团队

Question

我有一个游戏，我的团队包含许多用户。每个团队中的用户数量不同，但对于任何团队而言，用户的组合使该团队独一无二。

我为此创建了三个数据库表。

 Team    User    TeamUsers
 t_id    u_id    t_id, u_id

如果用户 1、4、5 和 7 以前曾在一个团队中，我想知道该团队的团队 ID。 SQL 看起来像什么？

我试过这样的东西:

SELECT t_id FROM teamusers WHERE u_id IN (users[1], ..., users[i])
GROUP BY t_id HAVING COUNT(t_id) = users[].length;

users 数组以编程方式填充值。这看起来正确吗？还有其他更好的方法吗？

Answer 1

这是关系划分的经典案例。在这个密切相关的问题下，我们收集了一整套查询:
How to filter SQL results in a has-many-through relation

让所有拥有所有给定球员的球队的一种方法是您已经拥有的球员。应该是最有效的查询之一。

对于较长的数组，unnest() + JOIN 表现更好:

SELECT t_id
FROM  (SELECT unnest($users) AS u_id) u
JOIN   team_users USING (u_id)
GROUP  BY 1
HAVING count(*) = array_length($users, 1);

但对于一手牌的玩家来说，这并不重要。
对于重复使用，您可以创建一个表函数，而不是在您的应用中构建查询:

CREATE OR REPLACE FUNCTION f_get_teams(_users int[])
  RETURNS SETOF int AS
$func$

SELECT t_id
FROM  (SELECT unnest($1) AS u_id) u
JOIN   team_users USING (u_id)
GROUP  BY 1
HAVING count(*) = array_length($1, 1);

$func$ LANGUAGE SQL STRICT;

调用:

SELECT * FROM f_get_teams('{1,4,5,7}'::int[]);