sql - 子查询 - 获得最高分

Question

我正在努力让期末考试得分最高的学生

首先我选择

SELECT s.STUDENT_ID, w.LAST_NAME,w.FIRST_NAME, MAX(s.NUMERIC_GRADE) AS NUMERIC_FINAL_GRADE
FROM GRADE s , SECTION z, STUDENT w
WHERE s.SECTION_ID = z.SECTION_ID AND s.STUDENT_ID = w.STUDENT_ID
AND z.COURSE_NO = 230 AND z.SECTION_ID = 100 AND s.GRADE_TYPE_CODE = 'FI'
GROUP BY s.STUDENT_ID, w.FIRST_NAME,w.LAST_NAME

它给了我这个结果，这就是我想要的

 STUDENT_ID LAST_NAME                 FIRST_NAME                NUMERIC_FINAL_GRADE
  ---------- ------------------------- ------------------------- -------------------
   262 Walston                   Donna                                      85 
   141 Boyd                      Robert                                     84 

但是当我尝试从这两个中获取最大值时，它没有给我任何行或一个错误

i.STUDENT_ID, k.LAST_NAME,k.FIRST_NAME
FROM GRADE i , SECTION j, STUDENT k
WHERE i.SECTION_ID = j.SECTION_ID AND i.STUDENT_ID = k.STUDENT_ID
AND j.COURSE_NO = 230 AND j.SECTION_ID = 100 AND i.GRADE_TYPE_CODE = 'FI' 
GROUP BY i.STUDENT_ID, k.FIRST_NAME,k.LAST_NAME
HAVING COUNT(*) =
(SELECT MAX(NUMERIC_FINAL_GRADE)
 FROM
(SELECT s.STUDENT_ID, w.LAST_NAME,w.FIRST_NAME, MAX(s.NUMERIC_GRADE) AS NUMERIC_FINAL_GRADE
FROM GRADE s , SECTION z, STUDENT w
WHERE s.SECTION_ID = z.SECTION_ID AND s.STUDENT_ID = w.STUDENT_ID
AND z.COURSE_NO = 230 AND z.SECTION_ID = 100 AND s.GRADE_TYPE_CODE = 'FI'
GROUP BY s.STUDENT_ID, w.FIRST_NAME,w.LAST_NAME))

ORDER BY i.STUDENT_ID, k.LAST_NAME,k.FIRST_NAME;

我怎样才能从我已经拥有的这两个结果中获得最大结果，为什么它没有给我任何行或错误？

Answer 1

传统方法是 analytic MAX() (或其他分析函数):

select *
  from ( select s.student_id
              , w.last_name
              , w.first_name
              , s.numeric_grade
              , max(s.numeric_grade) over () as numeric_final_grade
           from grade s
           join section z
             on s.section_id = z.section_id
           join student w
             on s.student_id = w.student_id
          where z.course_no = 230 
            and z.section_id = 100 
            and s.grade_type_code = 'FI'
                )
 where numeric_grade = numeric_final_grade

但我可能更喜欢使用 FIRST (保持)。

select max(s.student_id) keep (dense_rank first order by s.numeric_grade desc) as student_id
     , max(w.last_name) keep (dense_rank first order by s.numeric_grade desc) as last_name
     , max(w.first_name) keep (dense_rank first order by s.numeric_grade desc) as first_na,e
     , max(s.numeric_grade_name) as numeric_final_grade
  from grade s
  join section z
    on s.section_id = z.section_id
  join student w
    on s.student_id = w.student_id
 where z.course_no = 230 
   and z.section_id = 100 
   and s.grade_type_code = 'FI'

与您最初建议的方法相比，这两种方法的好处是您只扫描表一次，无需再次访问表或索引。我强烈推荐Rob van Wijk's blog post关于两者的区别。

附言这些将返回不同的结果，因此它们略有不同。如果两名学生的最高分相同，则分析函数将保留重复项(这也是您的建议所做的)。聚合函数将删除重复项，并在出现平局时返回随机记录。