,假设我在sql中有一个table1。
Team PlayerID
Team1 12
Team2 56
Team1 78
Team1 96
Team3 23
Team2 45
Team3 89
Team3 78
现在我有一个新的table2 at
PlayerID Gender
12 Male
56 Female
78 Female
96 Male
23 Female
45 Male
89 Female
78 Female
有没有办法列出只有女性的所有团队?
您可以使用join
,group by
和having
:
select t.team
from teams t join
players p
on p.playerid = t.playerid
group by t.team
having min(gender) = max(gender) and min(gender) = 'Female';
如果您确定每队至少有1个球员,则应该有效:
SELECT T1.Team
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.PlayerID = T2.PlayerID AND t2.Gender = 'Male'
GROUP BY T1.Team
Having COUNT(Gender) = 0