sql case when用法_从零学会SQL：多表查询

一、表的加法

1.union

union将两个表的数据按行合并在一起，两个表重复的数据只保留一个。

select 课程号,课程名称
from course
union
select 课程号,课程名称
from course1;

查询结果：

2.union all

union all将两个表的数据合并在一起并保留重复行。

select 课程号,课程名称
from course
union all
select 课程号,课程名称
from course1;

二、表的联结

1.交叉联结

交叉联结又称笛卡尔积，如下图所示，交叉联结是将表中的每一行都与另一个表中的每一行合并在一起：

交叉联结结果的行数是两张表中行数的乘积。交叉联结是后面介绍的所有联结的基础，后面学习的其他联结是在交叉联结的基础上加了过滤条件。

2.内联结

案例：

内联结查找出同时存在于两张表中的数据，内联结的sql查询语句为：

select a.学号,a.姓名,b.课程号
from student as a inner join score as b
on a.学号 = b.学号;
-- on子句表示两张表通过学号这一列匹配关系

3.左联结

案例：

在上面的左联结中，第一步：两个表通过学号产生匹配关系，将左侧的表作为主表，主表中的数据全部读取出来，右侧的表中只选出和主表相同学号的行；第二步：将两个表取出的数据合并，合并的方式为交叉联结。

左联结的sql查询语句为：

select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号 = b.学号;

如果我们想要得到的是下图中阴影部分的数据，则sql查询语句变为：

select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号 = b.学号
where b.学号 is null;

4.右联结

案例：

在上面的右联结中，第一步：两个表通过学号产生匹配关系，将右侧的表作为主表，主表中的数据全部读取出来，左侧的表中只选出和主表相同学号的行；第二步：将两个表取出的数据合并，合并的方式为交叉联结。

右联结的sql查询语句为：

select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号 = b.学号;

如果我们想要得到的是下图中阴影部分的数据，则sql查询语句变为：

select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号 = b.学号
where a.学号 is null;

5.全联结

案例：

全联结返回两张表中的所有行。两张表通过学号产生匹配关系，当某行和另一个表中有匹配时，两个表中匹配的数据进行合并，合并的方式为交叉联结。如果某行和另一个表中没有匹配，另一个表中对应的地方则用空值来填充。

注：MySQL是不支持全联结的，所以这里只需理解全联结的概念即可。

三、联结应用案例

案例1：查询所有学生的学号，姓名，选课数，总成绩

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

案例2：查询平均成绩大于85的所有学生的学号，姓名和平均成绩

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

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

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

四、case表达式

1.什么是case表达式？

使用case表达式可以帮助我们解决复杂的查询问题，case表达式的作用就相当于进行一个条件判断，判断某行数据是否符合某个条件。

case表达式的sql语句为：

case when <判断表达式> then <表达式>
     when <判断表达式> then <表达式>
     when <判断表达式> then <表达式>
     ...
     else <表达式>
end

when 后面的<判断表达式>用来判断某行数据是否符合某个条件，如果符合条件就运行后面的then子句，case表达式就运行到此结束了，不会再运行下一列的when子句了。如果不符合条件，就进入下一个when子句，如果直到最后的when子句也没有找到符合条件的数据，那么就会进行else后面的<表达式>。

2.案例

• 案例：在查询结果中显示成绩是及格还是不及格

select 学号,课程号,成绩,
(case when 成绩 〉= 60 then '及格'
      when 成绩 < 60 then '不及格'
      else null
 end) as 是否及格
from score;

运行结果：

• 案例：查询出每门课程的及格人数和不及格人数

select 课程号,
sum(case when 成绩 >= 60 then 1
         else 0
    end) as 及格人数,
sum(case when 成绩 < 60 then 1
         else 0
    end) as 不及格人数
from score
group by 课程号;

运行结果：

3.使用case表达式的注意事项

• else子句可以省略不写，这时默认else为空值，但是为了养成更好的书写习惯，建议不要省略这部分；
• 最后的end不可以省略；
• 前面我们都是把case表示式放到了select子句中，对查询结果进行了条件判断，其实case表达式可以写在sql语句的任意子句里面。

4.case表达式有什么作用？什么时候使用case表达式？

当有多种情况需要条件判断时就可以使用case表达式。

5.经典案例分析

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

本题涉及到自定义的分组，故肯定要使用case表达式：

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

注：使用group by子句时，select里面的列名只能是group by子句里的列名，所以这里group by子句中除课程号外还加入了课程名称这一列。但是这里加入的前提是要不影响分组结果，因为当使用多个列来分组时，这几个列的值全部相同才算一组。本案例中，课程号和课程名称是一一对应的，所以加上课程名称这一列对分组结果没有影响。

五、完成sqlzoo平台select from world中的练习题11-13

练习题网址为：SQLZOO:SELECT from WORLD Tutorial/zh

 -- 11.显示以N开头的国家的名字和该国家所属的洲名称,但用澳大拉西亚(Australasia)代替大洋洲(Oceania)
select name,
(case when continent = 'Oceania' then 'Australasia'
      else continent
end)
from world
where name like 'N%';

/*
12.显示以A或B开头的国家的国家名字和该国家所属的洲名称，
但用欧亚大陆(Eurasia)代替欧洲(Europe)和亚洲Asia;
用美洲(America)代替北美(North America)、南美(South America)或加勒比海(Caribbean)的每个国家。
*/
select name,
(case when continent = 'Europe' or continent = 'Asia' then 'Eurasia'
      when continent = 'North America' or continent = 'South America' or continent = 'Caribbean' then 'America' 
      else continent
end)
from world
where (name like 'A%') or (name like 'B%');

/*
13.将洲名称进行纠正：
大洋洲(Oceania)改为澳大拉西亚(Australasia);
欧亚大陆(Eurasia)的国家和土耳其(Turkey)改为欧洲/亚洲(Europe/Asia);
以B开头的加勒比海(Caribbean)岛屿改为北美(North America)，
其他的加勒比海(Caribbean)岛屿改为南美(South America).
显示所有国家的名称，原始大陆和新大陆。
*/
select name,continent,
(case when continent = 'Oceania' then 'Australasia'
      when continent = 'Eurasia' or name = 'Turkey' then 'Europe/Asia'
      when continent = 'Caribbean' and name like 'B%' then 'North America'
      when continent = 'Caribbean' and name not like 'B%' then 'South America' 
      else continent
end)
from world;

六、完成sqlzoo平台join中的练习题1-13

练习题网址为：The JOIN operation/zh

-- 1. 在进球信息表(goal)中查找德国球队进球的比赛编号，进球球员姓名
select matchid,player 
from goal
where teamid = 'GER';

-- 2.在比赛信息表(game)中查找比赛编号1012的信息，包括：比赛编号, 比赛地点以及对战双方的球队编号
select id,stadium,team1,team2
from game
where id = '1012';

-- 3.查找德国队进球球员姓名，球队编号, 比赛地点，比赛日期
select b.player,b.teamid,a.stadium,a.mdate
from game as a inner join goal as b on a.id = b.matchid
where b.teamid = 'GER';

-- 4. 查找姓名中以Mario开头的进球球员，以及这些球员参加比赛的对战双方
select a.team1,a.team2,b.player
from game as a inner join goal as b on a.id = b.matchid
where b.player like 'Mario%';

-- 5.查找进球球员的姓名、球队编号、教练、进球时间。要求进球时间<=10分钟
select a.player,a.teamid,b.coach,a.gtime
from goal as a inner join eteam as b on a.teamid = b.id
where a.gtime <= 10;

-- 6.查找'Fernando Santos'作为team1教练的比赛日期，球队名称
select a.mdate,b.teamname
from game as a inner join eteam as b on a.team1 = b.id
where b.coach = 'Fernando Santos';

-- 7.查找在比赛地点'National Stadium, Warsaw'的进球球员
select b.player
from game as a inner join goal as b on a.id = b.matchid
where a.stadium = 'National Stadium, Warsaw';

-- 8.查找射入德国球门的球员姓名
select distinct b.player
from game as a inner join goal as b on a.id = b.matchid
where (a.team1 = 'GER' or a.team2 = 'GER') and b.teamid <> 'GER';

-- 9.查找出球队名称和各球队进球总数
select b.teamname,count(a.gtime)
from goal as a inner join eteam as b on a.teamid = b.id
group by b.teamname;

-- 10.查找出所有比赛地点以及每个比赛地点的进球数
select a.stadium,count(b.gtime)
from game as a inner join goal as b on a.id = b.matchid
group by a.stadium;

-- 11.查找出有波兰球队'POL'参加的比赛编号，比赛日期以及对应这场比赛的进球数
select a.id,a.mdate,count(b.gtime)
from game as a inner join goal as b on a.id = b.matchid
where a.team1 = 'POL' or a.team2 = 'POL'
group by a.id;

-- 12.对于德国队'GER'得分的每场比赛，显示比赛编号，比赛日期和'GER'得分的进球数
select a.id,a.mdate,count(b.gtime)
from game as a inner join goal as b on a.id = b.matchid
where b.teamid = 'GER'
group by a.id;

-- 13.查找出所有比赛的日期，每场比赛中对战双方各自的进球数(也就是team1进球数，team2进球数)
select a.mdate,
a.team1, sum(case when b.teamid = a.team1 then 1
                  else 0
             end) as score1,
a.team2, sum(case when b.teamid = a.team2 then 1
                  else 0
             end) as score2
from game as a inner join goal as b on a.id = b.matchid
group by a.id
order by a.mdate,a.id,a.team1,a.team2;