case when 子查询_MySQL之多表查询

一、表的加法（union、union all ）

union : 会删除两张表里重复的部分 ；union all : 会保留两张表里重复的部分

SELECT cid, ccourse 
FROM course
UNION ALL
SELECT cid, ccourse 
FROM course1;

二、表的联结(join)

student

score

course

• 交叉联结(语法：cross join)：交叉联结是对两张表中的全部记录进行交叉组合，又称为笛卡尔积，其结果中的记录数通常是两张表中行数的乘积。但是这种联结在实际业务中并不会使用，这其中有两个原因：一是其结果没有实用价值；二是由于其结果行数太多，需要花费大量的运算时间和高性能设备的支持。
• 内联结(语法：inner join)：查找出同时存在于两张表中的数据并将它们交叉联结。

SELECT a.sid,a.sname,b.scourse
FROM student AS a
INNER JOIN score AS b
ON a.sid=b.sid

• 左联结(语法：LEFT JOIN)：将左侧表中的数据全部取出来，右侧表只取出和左侧表相对应的数据，最后进行交叉联结。（只会改变右侧表）

SELECT a.sid,a.sname,b.scourse
FROM student AS a
LEFT JOIN score AS b
ON a.sid=b.sid

SELECT a.sid,a.sname,b.scourse
FROM student AS a
LEFT JOIN score AS b
ON a.sid=b.sid
WHERE b.sid is NULL-- 与上相比，在左联结的基础上去掉了右边表格和左边表格相关联的数据

• 右联结(语法：right join)：和左联结相反，取出右侧表中的全部数据，左侧表只取出和右侧表相对应的数据，最后进行交叉联结。
• 全联结(语法：full join)：将左右表全部数据取出，匹配进行交叉联结，如果没有匹配数据，对应的数据用空值来填充。MySQL不支持全联结，理解概念即可。
• 练习

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

SELECT a.sid,a.sname,count(b.scourse),sum(b.sresult)
FROM student AS a
LEFT JOIN score AS b
ON a.sid=b.sid
GROUP BY a.sid;

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

SELECT a.sid,a.sname,c.cid,c.ccourse
FROM student AS a
INNER JOIN score AS b
ON a.sid=b.sid
INNER JOIN course AS c
ON b.scourse=c.cid;

三、CASE表达式

语法及作用：使用CASE表达式可以帮助我们解决复杂的查询问题，相当于条件判断的函数，判断每一行是不是满足条件。

CASE 表达式首先对最初的WHEN子句中的“when_value”进行求值开始执行，如果结果为真(TRUE)，那么就返回THEN子句中的“statement_list“；如果结果不为真，那么就跳转到下一条WHEN子句的“when_value”；如果直到最后的WHEN子句为止返回结果都不为真，那么就会返回ELSE中的“statement_list“，执行终止。

注意：

1. 虽然CASE表达式中的ELSE子句可以省略，但为了让SQL语句更加容易理解，还是尽量不要省略
2. CASE 表达式中的 END 不能省略

CASE 
    WHEN when_value THEN statement_list
    WHEN when_value THEN statement_list
    ELSE statement_list
END CASE;

练习：查询每门课程的及格人数和不及格人数

SELECT scourse,
sum(CASE WHEN sresult<60 THEN 1
	  ELSE 0 
		END) AS '及格人数',
sum(CASE WHEN sresult>=60 THEN 1
	  ELSE 0 
		END) AS '不及格人数'
FROM score
GROUP BY scourse;

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

SELECT a.scourse,b.ccourse,
sum(CASE WHEN a.sresult BETWEEN 85 AND 100 THEN 1
	  ELSE 0 
		END) AS '100-85',
sum(CASE WHEN a.sresult BETWEEN 70 AND 85 THEN 1
	  ELSE 0 
		END) AS '85-70',
sum(CASE WHEN a.sresult BETWEEN 60 AND 70 THEN 1
	  ELSE 0 
		END) AS '70-60',sum(CASE WHEN a.sresult<60 THEN 1
	  ELSE 0 
		END) AS '<60'
FROM score AS a
RIGHT JOIN course AS b
ON a.scourse=b.cid
GROUP BY a.scourse,b.ccourse;

四、练习

表1：比赛信息表

列名含义：
id：比赛编号
mdate：比赛日期
stadium：比赛地点
team1：对战双方（球队编号1）
team2：对战双方（球队编号2）
表2：进球信息表

列名含义：
matchid：比赛编号
teamid：球队编号
player：进球球员的姓名
gtime：从开始比赛到进球多长时间，单位：分钟
表3：球队信息表

列名含义：
id：球队编号
teamname：球队名称
coach：教练
练习一：显示德国球队(teamid = 'GER')进球的比赛编号和进球球员姓名

SELECT matchid, player 
FROM goal 
WHERE teamid = 'GER';

练习二：显示比赛编号为1012的比赛地点和对战双方

SELECT id,stadium,team1,team2
 FROM game 
WHERE id='1012';

练习三：显示德国队进球球员姓名，球队编号，比赛地点以及比赛日期

SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON (id=matchid)-- 这种形式也可
WHERE teamid = 'GER';

练习四：显示姓名中以Mario开头的进球球员的名称及其参加比赛的对战双方

SELECT team1,team2,player
FROM game JOIN goal ON (id=matchid)
WHERE player LIKE 'Mario%';

练习五：显示比赛开始10分钟内进球的球员姓名、球队id，教练名称以及比赛开始至进球市场

SELECT player, teamid, coach , gtime
FROM goal JOIN eteam on teamid=id
WHERE gtime<=10;

练习六：显示Fernando Santos在team1执教的比赛日期和其执教队名

SELECT mdate, teamname
FROM game JOIN eteam ON (team1=eteam.id)
WHERE coach = 'Fernando Santos';

练习七：显示在比赛地点'National Stadium, Warsaw'进球的球员姓名

SELECT player
FROM game JOIN goal ON (id=matchid)
WHERE stadium ='National Stadium, Warsaw';

练习八：显示射入德国球门的球员姓名

SELECT distinct(player)-- 入球的球员可能有重复
FROM game JOIN goal ON matchid = id 
where (teamid = team1 and team2 = 'GER') or (teamid = team2 and team1 = 'GER');

练习九：列出隊伍名稱 teamname 和該隊入球總數

SELECT teamname, COUNT(teamid) as '入球总数'
FROM eteam JOIN goal ON eteam.id=teamid
GROUP BY teamname;

练习十：显示在每个球场的总进球次数

SELECT stadium, COUNT(player) AS '入球数字'
FROM game join goal ON id = matchid
GROUP BY stadium;

练习十一：显示有波兰球队'POL'参加的比赛编号，比赛日期，对应这场比赛的进球数

SELECT matchid,mdate, COUNT(player) AS '入球数字'
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid;

练习十二：显示德国队'GER'得分的每场比赛的比赛编号，比赛日期和德国队的总进球数

SELECT matchid,mdate, COUNT(player) AS '入球数字'
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'GER' OR team2 = 'GER') AND teamid='GER'
GROUP BY matchid;

练习十三：显示所有比赛的日期以及每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数

SELECT mdate, 
team1, sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) AS score1,
team2, sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) AS score2
FROM game LEFT JOIN goal ON matchid = id -- 如果不用left join 可能存在某场比赛双方都没有进球
GROUP BY id, mdate, team1, team2
ORDER BY mdate, id, team1, team2;