Oracle 相关子查询理解

       相关子查询：执行查询的时候先取得外层查询的一个属性值，然后执行与此属性值相关的子查询，执行完毕后再取得外层父查询的下一个值，依次再来重复执行子查询；
       不相关子查询：子查询的执行不需要提前取得父查询的值，只是作为父查询的查询条件。

      相关子查询作用其实就是分组，然后选取那些满足条件的记录。当然相关子查询都可以使用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;

表结构：