oracle case when 可以跟子查询_sql之多表查询

一、表的加法

关键词：

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

Union all 可以保留两个表里重复的行。

用union将两个表合并

select 课程号,课程名称,教师号
from course
union
select 课程号,课程名称,教师号
from course1

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

练习二: 表的连结

-- 内连接
SELECT a.学号,a.姓名,b.课程号
from students as a INNER JOIN score as b
on a.学号 = b.学号;

-- 左连接
SELECT a.学号,a.姓名,b.课程号
FROM students as a left JOIN score as b
on a.学号 = b.学号;

-- 右连接
SELECT a.学号,a.姓名,b.课程号
FROM students as a RIGHT JOIN score as b
on a.学号 = b.学号;

-- 只有左边学号
SELECT a.学号,a.姓名,b.课程号
FROM students as a left JOIN score as b
on a.学号 = b.学号
WHERE b.学号 is NULL;

各种联结的总结：

三、用SQL中的联结语句解决业务问题

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

SELECT a.姓名,a.学号,COUNT(b.课程号) AS 选课数,SUM(成绩) AS 总成绩
FROM students AS a LEFT JOIN score AS b 
ON a.学号 = b.学号
group by a.学号,b.课程号;

-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT a.学号,a.姓名,AVG(b.成绩)
FROM students AS a LEFT JOIN score AS b
ON a.学号 = b.学号
GROUP BY a.学号
HAVING AVG(b.成绩)>85;

-- 查询学生的选课情况：学号、姓名、课程号、课程名称
SELECT a.学号,a.姓名,b.课程号,c.课程名称
from (students as a LEFT JOIN score as b
on a.学号 = b.学号) LEFT JOIN course as c
on b.课程号 = c.课程号

四、Case表达式

Case表达式在区分情况时使用。

Case表达式的语法：

Case  when <判断表达式>  then <表达式>
      when <判断表达式>  then <表达式>
      when <判断表达式>  then <表达式>
...
Else <表达式>
End

其中的判断表达式类似 列=值，case表达式会从对最初的when子句中的“判断表达式”进行求值开始执行，如果结果为真，就返回then子句中的表达式，case表达式的执行到此为止。如果结果不为真，就转到对下一条when子句求值。如果直到最后的when子句返回结果都不为真，就返回else中的表达式。

• Else子句可以省略，表示默认else null，但是最好不要省略。
• End子句不可以省略。
• Case表达式可以书写在任意位置。
• Case表达式可以将select语句中的行列结果进行互换。

-- 将学生每门课程的得分用及格和不及格标识出来
SELECT a.姓名,b.课程号,(CASE WHEN b.成绩>60 THEN
		'及格'
	ELSE
		'不及格'
END )AS 是否及格
FROM students AS a LEFT JOIN score AS	b
on a.学号 = b.学号

-- 查询每门课程的及格人数和不及格人数
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 课程号;

-- 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数、课程号、和课程名称
SELECT a.课程号,b.课程名称,SUM(CASE WHEN a.成绩 BETWEEN 85 AND 100 THEN 1 ELSE 0 END)AS '[100-85]',
                           SUM(CASE WHEN a.成绩<85 and a.成绩>=70 THEN 1 ELSE 0 END)AS '[85-70]',
                           SUM(CASE WHEN a.成绩<70 and a.成绩>=60 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.课程号

五、SQLzoo练习题

end.