在一对多关系中选择COUNT个 [英] SELECT COUNT across one-to-many relationship

问题描述

对于两个表

播放器

和

团队

具有1-∞关系(球员与球队):您如何计算每个球队有多少球员?

With a 1-∞ relationship (player to team): how would you count how many players belong to each team?

尝试失败:

SELECT team.teamid, (SELECT COUNT(player.team) FROM player)
FROM team
LEFT JOIN player ON player.team = team.teamid

推荐答案

尝试

SELECT t.teamid, COUNT(p.team) player_count
FROM team t LEFT JOIN
     player p ON p.team = t.teamid
GROUP BY t.teamid

SQLFiddle

SQLFiddle

如果某些球队没有指派球员，它将给出正确的结果事件.为此，请参见sqlfiddle(第3队没有队员).

这篇关于在一对多关系中选择COUNT个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！