(My)SQL JOIN-获得具有完全指定成员的团队 [英] (My)SQL JOIN - get teams with exactly specified members

问题描述

假设表格

team: id, title
team_user: id_team, id_user

我想选择仅具有指定成员的团队.在此示例中，我希望只有一个ID为1和5的用户才是团队.我想出了这个SQL，但对于这样简单的任务来说似乎有点过头了.

I'd like to select teams with just and only specified members. In this example I want team(s) where the only users are those with id 1 and 5, noone else. I came up with this SQL, but it seems to be a little overkill for such simple task.

SELECT team.*, COUNT(`team_user`.id_user) AS cnt
FROM `team`
JOIN `team_user` user0 ON `user0`.id_team = `team`.id AND `user0`.id_user = 1
JOIN `team_user` user1 ON `user1`.id_team = `team`.id AND `user1`.id_user = 5
JOIN `team_user` ON `team_user`.id_team = `team`.id
GROUP BY `team`.id
HAVING cnt = 2

谢谢大家的帮助.如果您想实际尝试您的想法，可以使用示例数据库结构和数据，可在此处找到: http://down .lipe.cz/team_members.sql

Thank you all for your help. If you want to actually try your ideas, you can use example database structure and data found here: http://down.lipe.cz/team_members.sql

推荐答案

关于

SELECT *
FROM team t
JOIN team_user tu ON (tu.id_team = t.id)
GROUP BY t.id
HAVING (SUM(tu.id_user IN (1,5)) = 2) AND (SUM(tu.id_user NOT IN (1,5)) = 0)

我假设team_user(id_team，id_user)上的唯一索引.

I'm assuming a unique index on team_user(id_team, id_user).

这篇关于(My)SQL JOIN-获得具有完全指定成员的团队的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！