子查询-获得最高分 [英] subquery - getting the highest score

问题描述

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

I am trying to get the student that scored highest on the final exam

首先我选择

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

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

and it gives me this result and that is what I want

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

但是当我尝试从这两个中获取最大值时，却没有显示行或错误

but when I try to get the max from these two it gives me no rows or an error

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;

如何从已经拥有的这两个结果中获得最大结果，为什么它没有行或错误?

How can I get max result from these two results that I already have and why does it give me no rows or an error ?

推荐答案

传统方法是 MAX() (或其他分析功能):

The traditional method is an analytic MAX() (or other analytic function):

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 (保持).

But I would probably prefer using FIRST (KEEP).

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的博客文章，这两个.

The benefits of both of these approaches over what you initially suggest is that you only scan the table once, there's no need to access either the table or the index a second time. I can highly recommend Rob van Wijk's blog post on the differences between the two.

P.S.这些将返回不同的结果，因此它们略有不同.如果两个学生的最高分数相同，则分析功能将保持重复(这也是您的建议所要做的).聚合函数将删除重复项，并在出现平局的情况下返回随机记录.

P.S. these will return different results, so they are slightly different. The analytic function will maintain duplicates were two students to have the same maximum score (this is what your suggestion will do as well). The aggregate function will remove duplicates, returning a random record in the event of a tie.

这篇关于子查询-获得最高分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！