相关子查询:执行查询的时候先取得外层查询的一个属性值,然后执行与此属性值相关的子查询,执行完毕后再取得外层父查询的下一个值,依次再来重复执行子查询;
不相关子查询:子查询的执行不需要提前取得父查询的值,只是作为父查询的查询条件。
相关子查询作用其实就是分组,然后选取那些满足条件的记录。当然相关子查询都可以使用group by (非相关子查询)代替,个人喜欢首选相关子查询,看起来比较简洁一点,没有非相关语句繁琐。
--Oracle相关查询的使用
--相关子查询作用其实就是分组(可能是统计组内记录数量,求最大值、最小值、平均值,判断组内是否有记录(exists语句)等),
--然后返回那些满足条件的记录。
--1
--下面这条select语句就是以student_no分组,计算平均值,将成绩平均值小于60的学生记录选出来。
SELECT *
FROM hand_student_core t_out
WHERE 60 > (SELECT AVG(t_in.core)
FROM hand_student_core t_in
WHERE t_out.student_no = t_in.student_no);
--2
--下面这条语句则是以student_no分组,求每个学生的最高成绩。
SELECT *
FROM hand_student_core t_out
WHERE t_out.core = (SELECT MAX(t_in.core)
FROM hand_student_core t_in
WHERE t_out.student_no = t_in.student_no);
--3
--这条语句任然是以student_no分组,选取选修少于2门课程的学生记录。
SELECT *
FROM hand_student_core t_out
WHERE 2 > (SELECT COUNT(1)
FROM hand_student_core t_in
WHERE t_out.student_no = t_in.student_no);
--4
--这条语句还是以student_no分组,将没有选修c001课程的学生查询出来。
SELECT *
FROM hand_student_core t_out
WHERE NOT EXISTS (SELECT 'X'
FROM hand_student_core t_in
WHERE t_out.student_no = t_in.student_no
AND t_in.course_no = 'c001');
--5
--这次是以course_no分组,将每门课程的最高分查出来。
SELECT *
FROM hand_student_core t_out
WHERE t_out.core = (SELECT MAX(t_in.core)
FROM hand_student_core t_in
WHERE t_out.course_no = t_in.course_no);
补充内容:
--1.按各科平均成绩和及格率的百分数,按及格率高到低的顺序排序,显示(课程号、平均分、及格率)
--方法一:
WITH aa AS
(SELECT c.course_no,
AVG(c.core) avg1,
COUNT(*) num1
FROM hand_student_core c
GROUP BY c.course_no),
bb AS
(SELECT c2.course_no,
COUNT(*) count1
FROM hand_student_core c2
WHERE c2.core >= 60
GROUP BY c2.course_no)
SELECT aa.course_no, aa.avg1, nvl2(bb.count1 / aa.num1, bb.count1 / aa.num1, 0)
FROM aa
LEFT JOIN bb
ON aa.course_no = bb.course_no
ORDER BY nvl2(bb.count1 / aa.num1, bb.count1 / aa.num1, 0) DESC;
--方法二:(优先使用)
SELECT core1.course_no,
AVG(core1.core),
COUNT(CASE
WHEN core1.core >= 60 THEN
'X'
END) / COUNT(*) rate
FROM hand_student_core core1
GROUP BY core1.course_no order by rate desc;
--2.查询各科成绩前三名的记录:(不考虑成绩并列情况),显示(学号、课程号、分数)
SELECT *
FROM (SELECT core1.student_no,
core1.course_no,
core1.core,
row_number() over(PARTITION BY core1.course_no ORDER BY core1.core DESC) rn
FROM hand_student_core core1) t
WHERE t.rn <= 3;
表结构: