问题描述
我正在努力让在期末考试中得分最高的学生
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 ?
推荐答案
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.
这篇关于子查询-获得最高分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!