case when 子查询_SQL之多表查询篇

一、表的加法/组合查询

1.union

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

• course

• course1

合并：

2.union all

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

注意：作为集合运算对象的表的前提是列数要一致。运算结果会导致行的增减，但是不会导致列的增减。

二、表的联结

联结的分类：

1.交叉联结cross join

交叉联结又称笛卡尔积，交叉联结是对两张表中的全部记录进行交叉组合，结果中的记录数通常是两张表中行数的乘积。例如，表1有n条记录，表2有m条记录，如果将两张表交叉联结，就是将表1中的每一行和表2中的每一行两两合并在一起，结果就会有n*m条记录。

交叉联结是所有联结运算的基础，但是在实际业务中很少使用，一是因为其结果行数太多，需要花费大量的运算时间和高性能设备的支持；二是因为其结果实用价值不高。

2.内联结 inner join

内联结是查找出同时存在于两张表的数据。

实例：student和score这两张表相同的字段是“学号”，通过学号可以将两个表进行内联结。

student表

score表

3.左联结 left join

左联结可将from子句左侧的表中的数据全部取出来，与右边进行匹配，若右表无匹配项，则其值为空值Null。

如果两个表的数据有部分重叠，但是想要只取出from子句左侧的表独有的部分(如下图)，则需要再加一个where子句进行限定。

比如对于下面student表和score表，想要只取出student表中独有的部分，也就是只取出学号为0002、0003、0004的信息。

student表

score表

4.右联结 right join

右联结可将from子句右侧的表中的数据全部取出来，与左边进行匹配，若左表无匹配项，则其值为空值Null。

如果两个表的数据有部分重叠，但是想要只取出from子句右表独有的部分(如下图)，可以再加一个where子句进行限定。

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

5.全联结 full join

全联结会返回左表和右表中的所有行，如果两个表之间有匹配，则进行合并；当没有匹配行时，另一个表中对应的值用空值Null填充。

需要注意的是，mysql是不支持全联结的，这里了解这个概念即可。

SQL运行顺序

1）先运行子查询

2）在每个查询语句里的运行顺序：先运行from，where，group by，having这些子句，然后运行select子句，最后运行order by，limit子句。

三、联结应用案例

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

需要用到学生表与成绩表，这两张表通过学号联结。涉及到联结就要考虑用哪种联结呢？因为要查到每个学生的信息，也就是要保留学生表里的所有学号，所以要用左联结。

select 

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

select 

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

此题涉及三个表的联结，用到两个inner join

select 

四、case表达式

CASE 

case表达式的作用相当于一个条件判断的函数，用于判断每一行是否满足某一条件；如果满足when的判断表达式则继续运行then子句，然后case表达式结束，不会再运行后面的when子句了。如果未满足条件，则进行下一个when子句，如果直到最后的when子句也没有找到符合条件的数据，那么就会进行else后面的<表达式>。

注意事项：

（1）else 子句可以省略不写， 这时会默认else是空值，但是为了书写规范，尽量要写。

（2）最后的end不能省略。

（3）case表达式除了可以放在select子句中，还可以放在SQL的任意子句里面

实例1：对成绩表中的学生成绩进行及格或不及格的判断

-- 注意：case子句是select子句的一部分，因此不能忽略其前面的逗号

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

-- 注意下面语句的运行顺序：先按照课程号分组，然后运行case表达式，再运行求和函数sum

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

• 思路：

各分数段人数： case when 分情况讨论筛选出各分数段的学生，再用sum汇总及格人数

各分数段人数：用到score表 课程号和课程名称：用到course表 涉及到两个表查询所以要用联结。

分别统计课程号和课程名称的分数段情况 ：按course表的课程号和课程名称分组

为什么要用两个列来分组：查询结果要求显示出课程号和课程名称，select里的列名只能是group by子句里的列名，为了让查询结果显示出课程号和课程名称，所以要在group by子句里加入课程名称。这里加入课程名称的前提是课程号与课程名称是一对一的关系，所以去掉课程名称与加上课程名称对分组结果都是没有影响的；若不是一对一关系，加入后会改变分组结果，这时就不能随便加了。

SELECT 

三、SQLZOO平台The JOIN operation练习题

再补充一些比较好的联结的习题：SQLZOO平台More JOIN operations练习题

解法二：

SELECT 

解法二：同上，用三表联结