The JOIN operation

The JOIN operation -- SQLZOO-LMLPHP

注意：where语句中对表示条件的需要用单引号，下面的译文使用的是有道翻译如有不正确，请直接投诉有道

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

译文：修改后显示德国队所有进球的比赛id和球员名字。要确定德国球员，请检查:teamid = 'GER'

SELECT matchid, player FROM goal

 WHERE teamid = 'GER';

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

　　SELECT id,stadium,team1,team2

　　FROM game

　　WHERE id = 1012;

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

译文：修改它，显示球员，teamid，体育场和mdate为每个德国进球。

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

04.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%';

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

译文：显示球员，teamid，教练，在前10分钟内所有的进球gtime<=10

SELECT player, teamid,coach, gtime FROM goal JOIN eteam on teamid=id WHERE gtime<=10;

06.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 (team1=eteam.id) WHERE coach = 'Fernando Santos';

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

译文：列出球场为“华沙国家体育场”的比赛中每进一球的球员名单

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

08.Instead 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' AND teamid != 'GER') OR (team2='GER' AND teamid != 'GER');

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

译文：显示球队名称和进球总数

SELECT teamname, COUNT(player) 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(player) 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”的比赛，显示比赛id、日期和进球次数。

SELECT DISTINCT matchid, mdate, COUNT(teamid) 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”进球的比赛，显示matchid、比赛日期和“GER”进球的次数

SELECT DISTINCT matchid, mdate, COUNT(teamid) FROM game JOIN goal ON matchid = id WHERE ( team1 = 'GER' AND teamid = 'GER') OR (team2 = 'GER' AND teamid = 'GER') group by matchid, mdate;

13.Sort your result by mdate, matchid, team1 and team2.

译文：根据mdate, matchid, team1和team2对你的结果排序。

# 写不出来

练习网址：https://sqlzoo.net/wiki/The_JOIN_operation

——————————————————————————————————————————————————————————————————————————————————————————————————————————

The JOIN operation -- SQLZOO-LMLPHP

GER