case when 子查询_SQL多表查询
1. 表的加法 （union）
快速建立相同结构表（以course为例）：
course ---》右键单击---》复制表---》结构和数据---》对新表重命名---》改数据
1）union不保留重复行
2）union all 保留重复行
2. 表的联接
1） 交叉联接（cross join）：实际业务中应用比较少，但是其他联接的基础
2） 内联接（inner join）：交集
取数图例：
SQL语句：
select a.学号, a.姓名, b.课程号
from student as a inner join score as b
on a.学号 = b.学号;
3） 左联接（left join）
取数图例：
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） 右联接（right join）
取数图例：
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） 全联接（full join）：MySQL不支持全连接
取数图例：
联接总结图
3. 联接应用案例
问题1：查询所有学生的学号、姓名、选课数、总成绩
分析：
1）学号、姓名（student表）
2）选课数（每个学生的选课数、score表）
3）总成绩（每个学生的总成绩，score表，按学号分组，对成绩求和）见图：
SQL语句
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的所有学生的学号、姓名、平均成绩
分析：
1） 查询出所有学生的学号、姓名、平均成绩、学号、姓名（student表）
平均成绩（score表），按学号分组求平均成绩
2） 平均成绩 > 85SQL语句
select a.学号, a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
where avg(b.成绩) > 85
group by a.学号
having avg(b.成绩) > 85;
问题3：查询学生的选课情况：学号、姓名、课程号、课程名称
分析：
1） 学号、姓名（student表）
课程号（score表）
2） 课程名称（course表）SQL语句
select a.学号, a.姓名, b.课程号, c.课程名称
from student as a left inner join score as b
on a.学号 = b.学号
left inner join course as c
on b.课程号 = c.课程号;
4. case 表达式
case when (判断表达式) then (表达式)
when (判断表达式) then (表达式)
when (判断表达式) then (表达式)
…
else(表达式)
end例1：
select 学号,课程号,成绩,
(case when 成绩 >= 60 then '及格'
when 成绩 < 60 then '不及格'
else
end) as 是否及格
from score;
问题1：查询每门课程的及格人数和不及格人数
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 课程号;注：sum()不应写成sum ()
步骤见图：
注意事项：
1. else 子句可以默认不写，但是为了完整性，保留
2. end 不可省略不写
3. 该表达式可写在任何子句中
问题2：使用分段[100-85], [85-70], [70-60], [<60]来统计各科成绩，分别统计：各各分段人数、课程号和课程名称
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练习
1.列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = 'GER'
select matchid, player
from goal
where teamid = 'GER';
2. 在表格 game中找出賽事1012的資料。只顯示賽事1012的 id, stadium, team1, team2
select game.id, game.stadium, game.team1, game.team2
from game left join goal
on game.id = goal.matchid
where game.id = '1012' and goal.matchid = '1012'
and goal.player = 'Lars Bender';
3.顯示每一個德國入球的球員名，隊伍名，場館和日期。
select goal.player, goal.teamid, game.stadium, game.mdate
from game inner join goal (之前用left join，但此处为两表共有的部分)
on game.id = goal.matchid
where goal.teamid = 'Gre';
以上结果与答案不符，可能数据有改变。
4. 列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
select g1.team1, g1.team2, g2.player
from game as g1 right join goal as g2
on g1.id = g2.matchid
where g2.player like 'Mario%';名字的模糊查询。
5. 列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime
select g.player,g.teamid,e.coach,g.gtime
from goal as g left join eteam as e
on g.teamid = e.id
where g.gtime <= 10;指定on的联接条件不能忘写，答案显示为错误，但是其给出的答案只是三次行改变了相对位置。
6. 列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。
select g.mdate, e.teamname
from game as g left join eteam as e
on e.id = g.team1
where e.coach = 'Fernando Santos';
7. 列出場館 'National Stadium, Warsaw'的入球球員。
select g2.player
from game as g1 left join goal as g2
on g1.id = g2.matchid
where g1.stadium = 'National Stadium, Warsaw';
8. 以下例子找出德國-希臘Germany-Greece 的八強賽事的入球
修改它，只列出全部賽事，射入德國龍門的球員名字。
select player
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where (g1.team1 = 'Ger' or g1.team2 = 'Ger')
and g2.player not in (select player
from goal
where teamid = 'Ger');和给出的答案有出入，原因在于选取的名字应该用distinct
select distinct b.player
from game as a inner join goal as b on a.id = b.matchid
where (b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER');以上语句可去重。
9.列出隊伍名稱teamname和該隊入球總數
select e.teamname, count(g.teamid)
from goal as g inner join eteam as e
on g.teamid = e.id
group by e.teamname;
如果不用group by 分组，那么球队和进球数均为总数。
10. 列出場館名和在該場館的入球數字。
select g1.stadium, count(g2.matchid)
from game as g1 inner join goal as g2
on g1.id = g2.matchid
group by g1.stadium;
11. 每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
select g2.matchid, g1.mdate, count(g2.matchid)
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where g1.team1 = 'POL' or g1.team2 = 'POL'
group by g2.matchid;
12. 每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
select g2.matchid, g1.mdate, count(g2.teamid = 'GER')
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where (g1.team1 = 'GER' or g1.team2 = 'GER')
and g2.teamid = 'GER'
group by g2.matchid;
13. 每场比赛主客队进球数。
select g1.mdate, g1.team1,
sum(case when g1.team1 = g2.teamid then 1
else 0
end ) as score1,
g1.team2,
sum(case when g1.team2 = g2.teamid then 1
else 0
end ) as score2
from game as g1 left join goal as g2
on g1.id = g2.matchid
group by g1.mdate,g1.id,g1.team1,g1.team2
order by g1.mdate,g1.id,g1.team1,g1.team2;
此处自己问题在于，group by和order by 后之前没有添加g1.id,g1.team1,g1.team2；添加多个分组可避免同一天有多个比赛时，不知会只有一场比赛。