casewhen多条件查询_第5关《从零学会SQL：多表查询》练习题答案

复仇者联盟中多个人物之间有着关系，同样的，表和表之间也会有关系，这种关系在数据库里叫做联结(join)，多表查找也是通过联结来实现的。

这是《从零学会sql》系列课程第5节课《多表查询》的练习题，也是常考常考的面试题。

一、练习题

查询所有学生的学号、姓名、选课数、总成绩

selecta.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩from student as a left join score as bon a.学号 = b.学号group by a.学号;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select a.学号,a.姓名, avg(b.成绩) as 平均成绩from student as a left join score as bon a.学号 = b.学号group by a.学号having avg(b.成绩)>85;

查询学生的选课情况：学号，姓名，课程号，课程名称

select a.学号, a.姓名, c.课程号,c.课程名称from student a inner join score b on a.学号=b.学号inner join course c on b.课程号=c.课程号;

查询出每门课程的及格人数和不及格人数

- 考察case表达式select 课程号,sum(case when 成绩>=60 then 1  else 0  end) as 及格人数,sum(case when 成绩 < 60 then 1  else 0  end) as 不及格人数from scoregroup by 课程号;

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

-- 考察case表达式select a.课程号,b.课程名称,sum(case when 成绩 between 85 and 100  then 1 else 0 end) as '[100-85]',sum(case when 成绩 >=70 and 成绩<85  then 1 else 0 end) as '[85-70]',sum(case when 成绩>=60 and 成绩<70  then 1 else 0 end) as '[70-60]',sum(case when 成绩<60 then 1 else 0 end) as '[<60]'from score as a right join course as b on a.课程号=b.课程号group by a.课程号,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)

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

2. 在比赛信息表(game)查找比赛编号1012的信息

select id,stadium,team1,team2from game where id = 1012;

3. 查找德国队进球球员姓名，球队编号(在进球信息表goal), 比赛地点,比赛日期(在比赛信息表game)

问题分析：

进球球员姓名，球队编号(在进球信息表goal)

比赛地点,比赛日期(在比赛信息表game)

两个表如何联结？通过比赛编号

哪一种联结？需要两个表中共同部分，内联结

select b.player, b.teamid, a.stadium, a.mdatefrom game as a inner join goal as b on a.id=b.matchidwhere b.teamid = 'GER';

4. 查找姓名中以Mario开头的进球球员，符合条件球员参加比赛的对战双方

问题分析：

1)查找出进球球员姓名(进球信息表goal：进球球员姓名player)，

比赛的对战双方(比赛信息表game：对战双方team1,team2)

两个表如何联结？通过比赛编号

哪一种联结？需要两个表中共同部分，内联结

2)加入条件：进球球员姓名中以Mario开头，模糊查询：like 'Mario%'

select a.team1, a.team2, b.playerfrom game as a inner join goal as b on a.id=b.matchidwhere b.player like 'Mario%';

5. 查找进球球员的姓名、球队编号、教练、多长时间进球。要求多长时间进球<=10分钟

问题分析：

1)查找出进球球员的姓名、球队编号、多长时间进球(在进球信息表goal)

教练(在球队信息表eteam)

两个表如何联结？通过球队编号(进球信息表goal：teamid球队编码 = 球队信息表eteam：id球队编码)

哪一种联结？需要两个表中共同部分，内联结

2)加入条件：多长时间进球<=10分钟

select b.player, b.teamid, c.coach, b.gtimefrom goal as b inner join eteam as c on b.teamid=c.idwhere b.gtime<=10;

6. 'Fernando Santos'作为教练的比赛日期，球队编号有哪些？

问题分析：

1)查找出

教练(在球队信息表eteam)

比赛日期，球队编码(在比赛信息表game)

两个表如何联结？通过球队编号(比赛信息表game：team1对战双方的球队编码(team1或者team2的球队编码) = 球队信息表eteam：id球队编码)

哪一种联结？需要两个表中共同部分，内联结

2)加入条件：教练是'Fernando Santos'

select a.mdate, c.teamnamefrom game as a inner join eteam as c on a.team1=c.idwhere c.coach = 'Fernando Santos';

7. 在比赛地点'National Stadium, Warsaw'有哪些进球球员？

问题分析：

1)查找

比赛地点(在比赛信息表game)，进球球员(在进球信息表goal)

2)加入条件：比赛地点是'National Stadium, Warsaw'

select b.playerfrom game as a inner join goal as b on a.id=b.matchidwhere a.stadium = 'National Stadium, Warsaw';

8.射入德国球门的球员姓名

问题分析：

1)查找进球球员姓名，对战双方

2)条件：有一个球队是德国(球队编号'GER')，被射入了球

select distinct b.playerfrom game as a inner join goal as b on a.id = b.matchidwhere (b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.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))

select c.teamname, count(b.teamid)from eteam as c inner join goal as b on c.id=b.teamidgroup by b.teamid;

上面这么写是不对的，因为查下结果中要查出球队名称(teamname)。如果按球队编码(teamid)来分组，结果中没法显示球队名称。所以这里我们用球队名称(teamname)来分组。正确的sql是：

select c.teamname, count(c.teamname)from eteam as c inner join goal as b on c.id=b.teamidgroup by c.teamname,;

10. 查找出所有比赛地点，每个比赛地点的进球数

问题分析：

比赛地点(比赛信息表game)，和每个比赛地点的进球数(进球信息表goal：按比赛地点分组，对进球人员进行汇总count(player))

联结方式是什么？要查出比赛信息表game中全部比赛地点，所以是左联结

select a.stadium, count(b.player)from game as a left join goal as b on a.id=b.matchidgroup by a.stadium;

11. 查找出有波兰球队'POL'参加的比赛编号，比赛日期，对应这场比赛的进球数

select a.id,a.mdate, count(b.player)from game as a inner join goal as b on a.id = b.matchid where (team1 = 'POL' OR team2 = 'POL')group by a.id;

上面sql运行后会报错，是因为分组结果中没有比赛日期(mdate)，而查询结果要求有比赛日期(mdate)。

为了让查询结果中有比赛日期(mdate)，我们在分组group by子句中再加上分组比赛日期(mdate)。

这时候group by a.id,a.mdate 有两个分组列，表示这两个列的值都相同时才算一组。因为比赛编号(id)，比赛日期(mdate)都是一对一的，不会改变之前的分组结果，但有可以让查询结果中有分组中的列。正确的sql：

select a.id,a.mdate, count(b.player)from game as a inner join goal as b on a.id = b.matchid where (team1 = 'POL' or team2 = 'POL')group by a.id,a.mdate;

12. 对于德国队'GER'得分的每场比赛，显示比赛编号，比赛日期和'GER'得分的进球数

问题分析：

比赛编号，比赛日期(在比赛信息表game)和'GER'得分的进球数(进球信息表goal：按比赛地点分组，对进球人员进行汇总count(player))

select a.id,a.mdate,count(player) from game as a inner join goal as b on a.id=b.matchid where b.teamid='GER' group by a.id,a.mdate;

13. 查找出所有比赛的日期，每场比赛中对战双方各自的进球数(也就是team1进球数，team2进球数)

问题分析：

当比赛信息表game中的球队编号(team1)也出现在进球信息表(goal)中时，表示这个球队进球了，使用case语句来统计

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

推荐：如何用最短的时间找到初级数据分析师工作？