sql join中能否使用case when_SQL多表查询

一、表的加法

将course表复制结构和数据，并修改内容，成为course1表

union可将表剔除重复记录后合并。这里指定了列名，是考虑到表结构未知的情况。union all将表剔除重复记录后合并

二、表的联结

有5种连接类型:

1）交叉联结 (cross join)：笛卡尔积，表A每一行和表B每一行合并。如：表A有3行，表B有2行，合并后有3*2=6行

文氏图

2）内联结 (inner join)：查找同时存在于两张表中的数据

文氏图

用内连接命令，从student表中查询学号和姓名，从score表查询课程号

3）左联结 (left join)：有两种类型

文氏图（1）

文氏图（2）

具体分3步。1、将左边的表做主表，取其全部。用左右张表共有的某字段做索引，找出右表中对应的信息。2、剔除左表中和右表重合的信息，即两表共同部分。3、将1和2结果做交叉联结

用左连接命令，从student表中查询学号和姓名，从score表查询课程号

因红框内出现null，说明学号为0004，姓名为王思聪，在score表中课程号字段无匹配记录。又因学号是用来联结student、score两表的索引字段，那指定score表中学号为null，再次运行左联结，查询分别存在于2张表的学号、姓名和课程号

说明score表中不存在为0004的学号。注：若写成学号=null，其结果是显示score表中不存在的学号（为null）对应的学号、姓名和课程号内容，并无意义

4）右联结 (right join)：有两种类型

文氏图（1）：

文氏图（2）：

也是3步。1、将右边的表做主表，取其全部。用左右张表共有的某字段做索引，找出左表中对应的信息。2、剔除左表中和右表重合的信息，即两表共同部分。3、将1和2结果做交叉联结

用右连接命令，从student表中查询学号和姓名，从score表查询课程号

换从score表中找到学号和课程号，再展示student表相应的姓名试试

得到的结果相同

同理，在score是主表的条件下，聚焦student表中学号为null的，student表中学号和姓名，score表中课程号字段，再运行右联结

把红框处换成score表，或是把那个is null换成=null，结果都一样。因为不是显示不存在于score表的学号及其他记录，就是显示不存在于student表的学号及其他

5）全联结 (full join)：有两种类型

文氏图（1）

返回左表和右表中的所有行。当左表和右表中某行匹配时，两行合并。当左表和右表中某一表中的行不匹配时，不存在的记录处用null填充。MySQL不支持全联结

文氏图（2）

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

思路：学号、姓名、选修课和总成绩——学号是主键，分别存在于student和score表。姓名存在于student表，课程号、成绩存在于score表，应使用表联结；所有学生——因student表中学号数量多而全，left join；选课数目和总成绩——count（课程号）sum（成绩）group by 学号；最后，分别给两表和字段起别名

红框绿框处结果不同，因count（null）恒为0。注：count(1)与count(*)得到的结果一致，包含null值。count(字段)不计算null值

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

思路：姓名在student表，平均成绩avg（成绩），成绩在score表；平均成绩大于85的——是先决条件，所以score表是主表，right join。学号是主键，分别存在于student和score表。所有学生的——group by 学号；最后，分别给两表和字段起别名

用score表的学号：

用student表的学号：

结果一样。因学号在两表中都存在，所以需要指定。而姓名字段仅存在于student表，无需申明

3、查询学生的选课情况，包含学号、姓名、课程号、课程名称字段

方法1

思路：学号存在于student和score两表，姓名在student表，课程号在score表和course表两中，课程名称字段在course表中；3表联结；以每个学生为单位——group by 学号，又以课程号为单位——group by 课程号；3表联结均为left join，联结词分别为学号和课程号；给3表分别起别名；学号和课程号两字段，需指明从哪张表中取

再贴一次student、score和course表

注：from后要把联结类型，和用于联结表的共同字段一次写完，才能跟group by。若需按学号分组，再按课程号分组，不能用and联结两个group by，而应该是group by 学号，课程号。红框处取score表，还是course表（用b还是c），结果一样

方法2：

其实，红框处可a可b，绿框处可b可c。显然，和方法1比，这个排序优，但inner join的性质决定其仅显示3表共同部分。也不需要上面group by子句排序

那么，再把方法1排序优化一下

若按score表的学号排序，即替换红框部分为b

因按score表中课程号为null，有优先排序的属性，所以姓名为王思聪的记录最先显示

三、用case表达式查询——当有多种条件判断时使用

1、查询学号、课程号、成绩和是否及格（建立一个名为是否及格的列，将判断该成绩是否及格的文字结果放入该列）

思路：学号、课程号、成绩均在score表里；是否及格——包含不及格（成绩<60）和及格（成绩>=60）两种条件判断，属多种条件判断，用case表达式

红框处null无法省略，且case表达式中的第2个when要和第1个when对齐，else要和when对齐,end要和case对齐，这种缩进无法省略

红框这种方式也不允许，case表达式要独占一行

也不能使用*拼接字段，如红框所示

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

思路：还是score一张表，查询课程和人数——select 课程号，人数；每门课程——group by 课程号；人数——count（学号）as 人数

及格人数和不及格人数——属多条件判断，用case表达式替换红框部分；而count（）的括号中仅能用列名或*，无法使用条件判断，需用sum（）函数完成计数；只能将及格条件和不及格条件均设定为1，方能计数：when 成绩>=60 then 1， when成绩<60 then 1；要想同时容纳2个设定为1的条件，势必要分写在两套判定条件里，外套2个sum函数，else处设为0：sum（case when 成绩>=60 then 1 else 0 end），sum（case when 成绩<60 then 1 else 0 end）；把第1个sum函数的结果起别名为及格人数，把第2个sum函数的结果起别名为不及格人数

使用case表达式注意事项：

1）case表达式中的else后跟null时，这个else null可省略，但为养成良好书写习惯，不要省略

2）case表达式中的end不能省略

3）在SQL语句中case表达式的位置不限

3、查询各科成绩的各分段人数、课程号和课程名称，其中成绩的各分段指：将成绩分成100-85组，85-70组，70-60组，<60组，共4个分段

思路：各科——group by 课程号；各分段人数——多条件判定+计数，sum函数套case表达式；成绩......课程号和课程名称——分处score和course2表，要给2表起别名并建立联结；因2表中含有的课程号一致，所以可左联结，也可右联结；最后给各分段人数起别名

图中a.成绩处的a.可省略，因为成绩字段并非在2表中都出现，无需指明；红框内可换成a.成绩>=85 and a.成绩<=100；黄框内可换成right；蓝框内可换成b；绿框内均为要细致小心避免遗漏处

注：原则上，因select 显示课程号和课程名称两字段，要和最后的group by后的课程号和课程名称两字段分组相对应，方能显示；但因此例中课程号和课程名称是一一对应的，则可将本应是group by a.课程号, b.课程名称句中，最后的b.课程名称省略。若课程号和课程名称非一一对应，则要用group by a.课程号, b.课程名称这种多列分组。而多列分组时，只有这几列的值完全相同才算一组

四、sqlzoo的join练习（为了省事，直接用了英文的）The JOIN operation

注：join即inner join

红框处可换为<>

提示说要在SELECT子句中用到count（*），指的是对goal表中的所有记录计数

红框处可换为*

两红框处要对应，即因select 中出现了两个列名（如第1个红框所示），group by不得不也跟两个相同列名（如第2红框所示）

虽然，红框处写为*可得出同样结果，但指明列名（如红框所示）更严谨

13、查询mdate、team1、score1、team2和score2字段，并用mdate、matchid、team1和team2分类

思路：显示mdate、team1、score1、team2和score2字段——mdate、team1、team2字段在game表中，score1和score2需要从goal表中计算。两表需使用联结，照搬其提示：FROM game JOIN goal ON matchid = id；score1和score2显示进球场次——多条件判定，用CASE表达式。语句参考提示CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1。提示说用sum计数——sum（case表达式）。因有两队，有2个sum（case表达式）。将2个sum（case表达式）分别起别名；用mdate、matchid、team1和team2分类——group by mdate,matchid,team1,team2

有点复杂，把它涉及的2表的内容贴一遍

注意是left join，而不是join，因为game表的id内容比goal表内容多；另，此处示范了如何不换行的书写case表达式