所以我是sql的初学者,我遇到了一个问题,我不知道如何检查哪些玩家拥有多个团队。
制作桌子后:
CREATE TABLE TEAM(TeamID varchar(10),
PlayerID varchar(10) REFERENCES "Player" ("playerID"),
TeamName varchar(15));
然后是值:
INSERT INTO Team VALUES ('t001', 'p001','teamname1');
INSERT INTO Team VALUES ('t001', 'p002','teamname1');
INSERT INTO Team VALUES ('t002', 'p003','teamname2');
INSERT INTO Team VALUES ('t002', 'p002','teamname2');
正如你所看到的:玩家2有更多的队伍,而其他人只有一支队伍。但是我该怎么写才能看到它呢?
您可以使用以下查询获得计数:
Select playerId,count(TeamId) from TEAM group by playerId having count(teamId)>=1
Select COUNT(PlayerId)
from Team
where PlayerId = 'some Id';
这将返回玩家参与的队伍数。
多种解决方案,具体取决于您想要的
球队中所有球员的列表以及他们在中的球队数量
Select playerId,count(*)
from TEAM
group by playerId
ORDER BY count(*) DESc
| PlayerID | count(*) |
|----------|----------|
| p002 | 2 |
| p001 | 1 |
| p003 | 1 |
只有几支球队中的球员
SELEcT playerId,count(*)
FROM TEAM
GROUP BY playerId
HAvING count(*) > 1
ORDER BY count(*) DESc
| PlayerID | count(*) |
|----------|----------|
| p002 | 2 |
团队中的球员,以及他们所在的团队
SELEcT playerId,count(*) , GROUP_concat(Teamname) AS teams
FROM TEAM
GROUP BY playerId
ORDER BY count(*) DESc
| PlayerID | count(*) | teams |
|----------|----------|---------------------|
| p002 | 2 | teamname1,teamname2 |
| p001 | 1 | teamname1 |
| p003 | 1 | teamname2 |
此外,如果你需要所有球员,即使他们不在一个团队中,你也应该进行相同的查询,但使用FROM Players LEFT JOIN Teams
,我不会隐藏更多,因为你没有显示你的球员表