SQL联接计数和分组依据
SQL联接计数和分组依据
提问于 2018-05-28 19:28:59
我有三张桌子
1. players(id, first_name, last_name, age, position, team_id) 2. teams(id, team_name, stadium, wins, draws,defeats,goal_for,goal_against) 3. goals_scored(id, player_id, goal_time)
SQL语句
SELECT
players.first_name,
players.last_name,
teams.name,
players.position,
players.age,
COUNT(*) AS goals
FROM
players
JOIN goals_scored
ON players.id = goals_scored.player_id
JOIN teams
ON players.team_id = teams.id
GROUP BY players.id;teams表
id team_name stadium wins draws defeats goal_for goal_against 1 APF Club Dasharath 7 2 7 29 25
players表
id first_name last_name position age team_id 4 Dipendra Shrestha forward 19 1
goals_scored表
id player_id goal_time 1 4 34 2 4 57
我想要对球员id进行分组，这样我就可以获得单个球员的进球数。喜欢
first_name last_name team_name position age goals Dipendra Shrestha APF Club forward 19 2
我该怎么做呢？
回答 1
Stack Overflow用户
发布于 2018-05-28 19:32:19
您的查询看起来基本上没问题。我会调整GROUP BY以使其更加完整：
SELECT p.first_name, p.last_name, t.name, p.position, p.age,
COUNT(*) AS goals
FROM players p JOIN
goals_scored gs
ON p.id = gs.player_id JOIN
teams t
ON p.team_id = t.id
GROUP BY p.first_name, p.last_name, t.name, p.position, p.age;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50565379
复制相关文章
相似问题
腾讯云开发者
