链接:https://sqlzoo.net/wiki/The_JOIN_operation
1.
SELECT matchid,player
FROM goal
WHERE teamid='GER'
SELECT id,stadium,team1,team2
FROM game
WHERE id='1012'
SELECT goal.player,goal.teamid,game.stadium,game.mdate
FROM game JOIN goal ON (game.id=goal.matchid)
WHERE goal.teamid='GER'
4.
SELECT game.team1,game.team2,goal.player
FROM game
JOIN goal ON game.id=goal.matchid
WHERE goal.player LIKE 'Mario%'
SELECT goal.player, goal.teamid, eteam.coach, goal.gtime
FROM goal
JOIN eteam ON goal.teamid=eteam.id
WHERE gtime<=10
SELECT game.mdate, eteam.teamname
FROM game
JOIN eteam ON game.team1=eteam.id
WHERE eteam.coach='Fernando Santos'
SELECT goal.player
FROM goal
JOIN game ON goal.matchid=game.id
WHERE game.stadium='National Stadium, Warsaw'
SELECT DISTINCT goal.player
FROM goal JOIN game ON goal.matchid=game.id
WHERE (game.team1='GER' OR game.team2='GER' )AND goal.teamid!='GER'
SELECT eteam.teamname, COUNT(goal.player)
FROM eteam JOIN goal ON eteam.id=goal.teamid
GROUP BY(eteam.teamname)
ORDER BY eteam.teamname
SELECT game.stadium, COUNT(goal.player)
FROM game
JOIN goal ON game.id = goal.matchid
GROUP BY(game.stadium)
SELECT goal.matchid, game.mdate, COUNT(goal.matchid) AS count
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY goal.matchid, game.mdate
SELECT game.id, game.mdate, COUNT(game.id)
FROM game JOIN goal ON game.id=goal.matchid
WHERE goal.teamid='GER'
GROUP BY game.id, game.mdate
SELECT game.mdate,
game.team1, SUM(CASE WHEN goal.teamid=game.team1 THEN 1 ELSE 0 END) AS score1,
game.team2, SUM(CASE WHEN goal.teamid=game.team2 THEN 1 ELSE 0 END) AS score2
FROM game LEFT JOIN goal ON game.id=goal.matchid
GROUP BY game.mdate, game.id, game.team1,game.team2
ORDER BY game.mdate, game.id, game.team1, game.team2