第5关《从零学会SQL：多表查询》练习题答案
复仇者联盟中多个人物之间有着关系，同样的，表和表之间也会有关系，这种关系在数据库里叫做联结（join），多表查找也是通过联结来实现的。
这是《从零学会sql》系列课程第5节课《多表查询》的练习题，也是常考常考的面试题。
一、练习题
查询所有学生的学号、姓名、选课数、总成绩
selecta.学号,a.姓名,count(b.课程号)as选课数,sum(b.成绩)as总成绩fromstudentasa leftjoinscoreasbona.学号 = b.学号groupbya.学号;
查询平均成绩大于85的所有学生的学号、姓名和平均成绩
selecta.学号,a.姓名,avg(b.成绩)as平均成绩fromstudentasaleftjoinscoreasbona.学号 = b.学号groupbya.学号havingavg(b.成绩)>85;
查询学生的选课情况：学号，姓名，课程号，课程名称
selecta.学号, a.姓名, c.课程号,c.课程名称fromstudent ainnerjoinscore bona.学号=b.学号innerjoincourse conb.课程号=c.课程号;
查询出每门课程的及格人数和不及格人数
- 考察case表达式select课程号,sum(casewhen成绩>=60then1else0end)as及格人数,sum(casewhen成绩 <60then1else0end)as不及格人数fromscoregroup by 课程号;
使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
-- 考察case表达式selecta.课程号,b.课程名称,sum(casewhen成绩between85and100then1else0end)as'[100-85]',sum(casewhen成绩 >=70and成绩<85then1else0end)as'[85-70]',sum(casewhen成绩>=60and成绩<70then1else0end)as'[70-60]',sum(casewhen成绩<60then1else0end)as'[<60]'fromscoreasarightjoincourseasbona.课程号=b.课程号groupbya.课程号,b.课程名称;
二、题目来自sqlzoo的多表查询题目
网址：
https://sqlzoo.net/wiki/The_JOIN_operation/zh
这部分题目使用的是‘2012年欧洲足球锦标赛’数据库里的表。
1.比赛信息表game
id比赛编号，mdate比赛日期， stadium比赛地点， team1对战双方（球队编号1）， team2对战双方（球队编号2）
2.进球信息表goal
matchid比赛编号，teamid球队编号， player进球球员的姓名，gtime多长时间进的球（从开始比赛到进球多长时间，单位：分钟）
比赛信息表game（id比赛编号）和进球信息表goal（matchid比赛编号）通过‘比赛编号’联结
3.球队信息表eteam
id球队编号（联结2） teamname 球队名称 coach教练
进球信息表goal（teamid球队编号）球队信息表eteam（球队编号id）通过‘球队编号’联结
如何做题？
练习题答案
先自己在sqlzoo平台里（网址：
https://sqlzoo.net/wiki/The_JOIN_operation/zh）
输入sql，查看运行结果，然后再和下面的答案对比哪里写的不一样
1. 在进球表（goal）中查找德国球队（teamid = 'GER'）进球的比赛编号（matchid），进球球员姓名（player）
selectmatchid, playerfromgoalwhereteamid ='GER';
2. 在比赛信息表（game）查找比赛编号1012的信息
selectid,stadium,team1,team2fromgamewhereid=1012;
3. 查找德国队进球球员姓名，球队编号（在进球信息表goal）, 比赛地点,比赛日期（在比赛信息表game）
问题分析：
进球球员姓名，球队编号（在进球信息表goal）
比赛地点,比赛日期（在比赛信息表game）
两个表如何联结？通过比赛编号
哪一种联结？需要两个表中共同部分，内联结
selectb.player, b.teamid, a.stadium, a.mdatefromgameasainnerjoingoalasbona.id=b.matchidwhereb.teamid ='GER';
4. 查找姓名中以Mario开头的进球球员，符合条件球员参加比赛的对战双方
问题分析：
1）查找出进球球员姓名（进球信息表goal：进球球员姓名player），
比赛的对战双方（比赛信息表game：对战双方team1,team2）
两个表如何联结？通过比赛编号
哪一种联结？需要两个表中共同部分，内联结
2）加入条件：进球球员姓名中以Mario开头，模糊查询：like 'Mario%'
selecta.team1, a.team2, b.playerfromgameasainnerjoingoalasbona.id=b.matchidwhereb.playerlike'Mario%';
5. 查找进球球员的姓名、球队编号、教练、多长时间进球。要求多长时间进球<=10分钟
问题分析：
1）查找出进球球员的姓名、球队编号、多长时间进球（在进球信息表goal）
教练（在球队信息表eteam）
两个表如何联结？通过球队编号（进球信息表goal：teamid球队编码 = 球队信息表eteam：id球队编码）
哪一种联结？需要两个表中共同部分，内联结
2）加入条件：多长时间进球<=10分钟
selectb.player, b.teamid, c.coach, b.gtimefromgoalasbinnerjoineteamasconb.teamid=c.idwhere b.gtime<=10;
6. 'Fernando Santos'作为教练的比赛日期，球队编号有哪些？
问题分析：
1）查找出
教练（在球队信息表eteam）
比赛日期，球队编码（在比赛信息表game）
两个表如何联结？通过球队编号（比赛信息表game：team1对战双方的球队编码（team1或者team2的球队编码） = 球队信息表eteam：id球队编码）
哪一种联结？需要两个表中共同部分，内联结
2）加入条件：教练是'Fernando Santos'
selecta.mdate, c.teamnamefromgameasainnerjoineteamascona.team1=c.idwherec.coach ='Fernando Santos';
7. 在比赛地点'National Stadium, Warsaw'有哪些进球球员？
问题分析：
1）查找
比赛地点（在比赛信息表game），进球球员（在进球信息表goal）
2）加入条件：比赛地点是'National Stadium, Warsaw'
selectb.playerfromgameasainnerjoingoalasbona.id=b.matchidwherea.stadium ='National Stadium, Warsaw';
8.射入德国球门的球员姓名
问题分析：
1）查找进球球员姓名，对战双方
2）条件：有一个球队是德国（球队编号'GER'），被射入了球
selectdistinctb.playerfromgameasainnerjoingoalasbona.id = b.matchidwhere(b.teamid = a.team1anda.team2 ='GER')or(b.teamid = a.team2anda.team1 ='GER');
1）解释下where子句中的条件：
主队是德国或者客队是德国，比如
德国 和 A对比赛（德国是主队），进球的是A队
A队和德国比赛（德国是客队），进球的是A队
所以条件是(b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER')
2）内联结的结果中入门球员有重复值，用distinct去掉重复值
9. 查找出球队名称，和每个球队进球人数
问题分析：
球队名称(球队信息表eteam)，和每个球队进球人数(进球信息表goal：按球队编号分组统计count(teamid))
selectc.teamname,count(b.teamid)frometeamascinnerjoingoalasbonc.id=b.teamidgroupbyb.teamid;
上面这么写是不对的，因为查下结果中要查出球队名称（teamname）。如果按球队编码(teamid)来分组，结果中没法显示球队名称。所以这里我们用球队名称（teamname）来分组。正确的sql是：
selectc.teamname,count(c.teamname)frometeamascinnerjoingoalasbonc.id=b.teamidgroupbyc.teamname,;
10. 查找出所有比赛地点，每个比赛地点的进球数
问题分析：
比赛地点(比赛信息表game)，和每个比赛地点的进球数(进球信息表goal：按比赛地点分组，对进球人员进行汇总count(player))
联结方式是什么？要查出比赛信息表game中全部比赛地点，所以是左联结
selecta.stadium,count(b.player)fromgameasaleftjoingoalasbona.id=b.matchidgroupbya.stadium;
11. 查找出有波兰球队'POL'参加的比赛编号，比赛日期，对应这场比赛的进球数
selecta.id,a.mdate,count(b.player)fromgameasainnerjoingoalasbona.id = b.matchidwhere(team1 ='POL'ORteam2 ='POL')groupbya.id;
上面sql运行后会报错，是因为分组结果中没有比赛日期（mdate），而查询结果要求有比赛日期（mdate）。
为了让查询结果中有比赛日期（mdate），我们在分组group by子句中再加上分组比赛日期（mdate）。
这时候group by a.id,a.mdate 有两个分组列，表示这两个列的值都相同时才算一组。因为比赛编号（id），比赛日期（mdate）都是一对一的，不会改变之前的分组结果，但有可以让查询结果中有分组中的列。正确的sql：
selecta.id,a.mdate,count(b.player)fromgameasainnerjoingoalasbona.id = b.matchidwhere(team1 ='POL'orteam2 ='POL')groupbya.id,a.mdate;
12. 对于德国队'GER'得分的每场比赛，显示比赛编号，比赛日期和'GER'得分的进球数
问题分析：
比赛编号，比赛日期（在比赛信息表game）和'GER'得分的进球数(进球信息表goal：按比赛地点分组，对进球人员进行汇总count(player))
selecta.id,a.mdate,count(player)fromgameasainnerjoingoalasbona.id=b.matchidwhereb.teamid='GER'groupbya.id,a.mdate;
13. 查找出所有比赛的日期，每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数）
问题分析：
当比赛信息表game中的球队编号(team1)也出现在进球信息表(goal)中时，表示这个球队进球了，使用case语句来统计
selecta.mdate,a.team1,sum(casewhena.team1=b.teamidthen1else0end)asscore1,a.team2,sum(casewhena.team2=b.teamidthen1else0end) score2fromgameasaleftjoingoalasbona.id = b.matchidgroupbya.id,a.mdate,a.team1,a.team2;
