SQLzoo The JOIN operation答案

1. Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER'

 SELECT matchid,player FROM goal 
   WHERE teamid = 'GER'

        2. Show id, stadium, team1, team2 for just game 1012

 SELECT id,stadium,team1,team2
   FROM game
 WHERE id = 1012

         3.Modify it to show the player, teamid, stadium and mdate for every German goal.

 SELECT player,teamid,stadium,mdate
   FROM game JOIN goal ON (id=matchid)
 WHERE teamid = 'GER'

        4.Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'

        显示球队1、球队2和球员，了解一名叫马里奥的球员打进的每一个进球

 SELECT team1,team2,player
 FROM game JOIN goal ON (id = matchid)
 WHERE player LIKE 'Mario%'

        5.Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

        显示前10分钟所有进球的player、teamid、coach、gtime gtime<=10`

 SELECT player, teamid, coach, gtime
   FROM goal JOIN eteam ON (id = teamid)
  WHERE gtime<=10

        6.List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.

        列出比赛日期和“费尔南多·桑托斯”担任球队1教练的球队名称。

 SELECT mdate,teamname
 FROM game g
 JOIN eteam e ON g.team1 = e.id
 WHERE e.coach = 'Fernando Santos'

        7.List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'

        列出体育场为“National Stadium, Warsaw”的比赛中每个进球的球员

 SELECT player FROM goal
 JOIN game ON matchid = id 
 WHERE stadium = 'National Stadium, Warsaw'

        8.The example query shows all goals scored in the Germany-Greece quarterfinal.

Instead show the name of all players who scored a goal against Germany.

        示例查询显示德国-希腊四分之一决赛中的所有进球。 相反，显示所有在对阵德国的比赛中进球的球员的名字。

 SELECT DISTINCT player 
 FROM goal 
 JOIN game ON matchid = id 
 WHERE teamid != 'GER' 
 AND (team1 = 'GER' or team2 = 'GER')

        9.Show teamname and the total number of goals scored.

        显示队名和进球总数。

 SELECT teamname, COUNT(*)
   FROM eteam JOIN goal ON id=teamid
 GROUP BY teamname

        10.Show the stadium and the number of goals scored in each stadium.

        显示体育场和每个体育场的进球数。

 SElECT stadium,COUNT(*)
 FROM game JOIN goal ON id = matchid
 GROUP BY stadium

        11.For every match involving 'POL', show the matchid, date and the number of goals scored.

        对于每场涉及“POL”的比赛，请显示匹配对象，日期和进球数。

 SELECT matchid,mdate, COUNT(matchid)
   FROM game JOIN goal ON matchid = id 
  WHERE (team1 = 'POL' OR team2 = 'POL')
 GROUP BY matchid,mdate

        12.For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'

        对于“GER”进球的每场比赛，显示匹配项，比赛日期和“GER”进球数

 SELECT matchid,mdate,COUNT(*)
 FROM goal
 JOIN game ON id = matchid
 WHERE teamid = 'GER'
 GROUP BY matchid,mdate

        13.List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises.

        列出每场比赛以及每支球队的进球数，如图所示。这将使用“CASE WHEN”，这在之前的任何练习中都没有解释过。

1. mdate	team1	score1	team2	score2
   1 July 2012	ESP	4	ITA	0
   10 June 2012	ESP	1	ITA	1
   10 June 2012	IRL	1	CRO	3
   ...

   Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

   请注意，在查询中，列出了每个目标。如果是球队1的进球，那么1分出现在比分1中，否则就是0。您可以对此列求和，以获取 team1 所得分的进球数。按日期、匹配 id、team1 和 team2 对结果进行排序。

注意：后面的应该是score2，这个地方写错了

 SELECT mdate,team1,
 SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) score1,
 team2,
 SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) score1
 FROM game
 LEFT JOIN goal ON matchid = id
 GROUP BY mdate,team1,team2