SQL Zoo 6.The JOIN operation

以下数据均来自SQL Zoo

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.From the previous query you can see that Lars Bender's scored a goal in game 1012. Now we want to know what teams were playing in that match.(从之前的查询中你可以看到拉斯·本德在1012场比赛中进了一个球。现在我们想知道那场比赛是哪支球队.)

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

3.the player (from the goal) and stadium name (from the game table) for every goal scored.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%'(显示team1, team2和球员的每一个进球被称为马里奥球员)

select team1,team2,player from game join 
goal on game.id = goal.matchid where player like 'Mario%'

5.Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10.(显示球员，球队，教练，gtime前10分钟内所有进球的gtime<=10)

select player,teamid,coach,gtime from goal 
join eteam on goal.teamid = eteam.id where gtime <=10

6.List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.(请列出比赛日期和“费尔南多·桑托斯”担任教练的球队名称)

select mdate,teamname from game 
join eteam on game.team1 = eteam.id where coach = 'Fernando Santos'

7.List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'(列出在比赛场地为“华沙国家体育场”的比赛中每个进球的球员)

select player from game 
join goal on game.id = goal.matchid where stadium = 'National Stadium, Warsaw'

8.Show the name of all players who scored a goal against Germany.(显示所有在对阵德国队的比赛中进球的球员的名字)

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

9.Show teamname and the total number of goals scored.(显示球队名称和总进球数)

SELECT teamname,count(teamid)
  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(matchid) from game 
join goal on game.id = goal.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(*) from game 
join goal on id = matchid where team1 = 'POL' or team2='POL' group by matchid

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(teamid) from game 
join goal on id = matchid where teamid = 'GER' group by matchid

13.List every match with the goals scored by each team as shown. 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.（列出每一场比赛，如果是team1的进球，则score1中显示1，否则显示0。您可以对这一列进行求和，以获得team1得分的总数。按候选人、配对、team1和team2对结果进行排序。）

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) score2
FROM game 
LEFT JOIN goal 
ON matchid = id
GROUP BY mdate, matchid, team1, team2