关于sqlzoo中的表连接(join)的练习题~

00. 前言

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

使用的为sqlzoo的MySQL库。

表1：game(id,mdate,stadium,team1,team2)

表2：goal(matchid,teamid,player,gtime)

表3：eteam(id,teamname,coach)

01.

Modify it to show the matchid and player name for allgoals scored by Germany. To identify German players,check for: teamid =’GER’.
修改它以显示德国所有进球的比赛号和队员的名字。要识别德国球员，请检查:teamid=’GER’
选择。

1
2
3

select matchid,player
from goal
where teamid='GER'

02.

Notice in the that the column matchid in the goal table corresponds to the id column in the game table. We can look up information about game 1012 by finding that row in the game table.
Show id, stadium, team1, team2 for just game 1012.
注意，goal表中的matchid列对应于game表中的id列，我们可以通过在game表中找到该行来查找关于1012游戏的信息。
显示仅1012场比赛的ID、体育场、球队1、球队2。

1
2
3

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.

修改它，以显示每个德国进球的球员、队名、球场和日期。

1
2
3

select player,teamid,stadium,mdate
from game join goal on game.id=goal.matchid
where teamid='GER'

04.

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

显示由一个叫Mario的球员打进的每一个球的队1，队2和球员。

1
2
3

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

05.

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

显示球员、队名、教练、前10分钟所有进球的进球时间。

1
2
3

select player,teamid,coach,gtime
from goal join eteam on goal.teamid=eteam.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.

列出Fernando Santos担任球队教练的比赛日期和球队名称。

1
2
3

select mdate,teamname
from game join eteam on game.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’.

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

1
2
3

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

08.

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

显示所有在对德国比赛中打进一球的队员的名字。

先在game表中查找team1h或team2队伍中包含德国的，得到对应的比赛id，
将比赛id匹配到goal表中，找到队伍中除了德国的每行，即可找到打败过德国队的运动员，
最后使用distinct对运动员名字去重。

select distinct player
from game join goal on game.id=goal.matchid
where (team1='GER' or team2='GER') and teamid!='GER'

09.

Show teamname and the total number of goals scored.

显示球队名称和进球总数。

1
2
3

select teamname,count(teamid) 进球总数
from goal join eteam on goal.teamid=eteam.id
group by teamname

10.

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

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

1
2
3

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”的比赛，显示比赛ID、日期和进球数（指赛事的总进球数而不是指“POL”的进球数）。

select matchid,mdate,count(teamid) 每场赛事进球数
from game join goal on game.id=goal.matchid
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”进球的每一场比赛，显示比赛ID、比赛日期和“GER”的进球数。

select matchid,mdate,count(teamid) 进球数
from game join goal on game.id=goal.matchid
where teamid='GER'
group by matchid,mdate

13.

List every match with the goals scored by each team as shown.

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

Some games have no goals scored by either side, so a regular join query will exclude them because they will not have related entries on the goal table.

如图所示列出每场比赛，并列出各队进球数。

查询中的通知中列出了每个目标。如果它是团队1的目标，则score1中会出现1否则会出现0。可以将该列求和，以获取团队1得分的目标数。按mdate,team1和 team2 对结果进行排序。

有些游戏中任何一方都没有进球，所以常规的join查询会将它们排除在外，因为它们在进球表上没有相关条目。需要使用left join。

方法1：使用case when exp then …… else …… end

select 
mdate,
team1,
sum(case when teamid=team1 then 1 else 0 end) as score1,
team2,
sum(case when teamid=team2 then 1 else 0 end) as score2
from game left join goal on game.id=goal.matchid
group by mdate,team1,team2
order by mdate,team1,team2

方法2：使用if(exp,exp为真时返回结果，exp为假时返回结果)

select 
mdate,
team1,
sum(if(teamid=team1,1,0) as score1,
team2,
sum(if(teamid=team2,1,0) as score2
from game left join goal on game.id=goal.matchid
group by mdate,team1,team2
order by mdate,team1,team2