我有个问题。(SQL生成器)。我有两张桌子。
玩家(nr,player_id,team_id,name,points,season)
团队(team_id、team_name、season)-有一个唯一的team_id,但根据不同的赛季,team_name可能不同。
我想在特定赛季对单个玩家的所有积分进行求和,我可以做到:)
SELECT SUM(players.points),
players.player_id,
players.team_id,
players.season,
players.nr,
players.name
FROM players
WHERE season='2015/2016'
GROUP BY player_id
ORDER BY SUM(players.points) DESC
LIMIT 30
这对我来说非常完美:)但是,我不知道如何加入团队表对应于特定季节的teams.team_name
与WHERE clousre匹配。在这种情况下,季节="2015/2016"非常特别。如果我想总结2015/2016赛季的球员积分,我需要从seasson 2015/2016获得team_name。我尝试了LEFT JOIN,但没有成功:/这是我的密码。
SELECT players.player_id,
players.nr,
players.name,
players.team_id,
SUM(players.points),
players.season
FROM players
WHERE players.sezon='2015/2016'
LEFT JOIN teams
ON (teams.team_id=players.team_id AND teams.season=players.season )
GROUP BY player_id
ORDER BY SUM(points) DESC
LIMIT 30
谢谢你的努力。
如果您不想在没有匹配团队记录的情况下显示玩家记录,请使用INNER JOIN而不是LEFT JOIN
SELECT players.player_id,
players.nr,
players.name,
players.team_id,
SUM(players.points),
players.season,
teams.team_name
FROM players
LEFT JOIN teams
ON (teams.team_id = players.team_id AND teams.season = players.season )
WHERE players.season = '2015/2016'
GROUP BY player_id
ORDER BY SUM(points) DESC
LIMIT 30