聚合函数，按最近日期 Oracle 连接

Question

我正在尝试按测试类型，测试元素，学期和ID为学生选择一个测试分数。 每个 ID 一个分数。 如果学生多次参加考试，我只想返回该测试和元素的最高(或最新(分数。

我的问题是，在极少数情况下(2000 年不到 10 条记录(，学生在不同的日期记录了两个考试成绩，因为他们重新参加了考试以提高他们的分数，而我们记录了两个分数。 因此，我的输出有少量记录，具有唯一name_ids的多个分数(其中test_id = 'act'，element_id = 'comp'(。

这两个表的示例如下：

students
----------------------------------------------------------
name_id     term_id
100         Fall
100         Spring
100         Summer
105         Fall
105         Spring
110         Fall
110         Spring
110         Summer
test_score
----------------------------------------------------------
name_id     test_id   element_id    score         test_date
100         act       comp          25            02/01/2019
100         sat       comp          1250          01/20/2019
105         act       comp          19            01/15/2019
105         act       comp          21            02/28/2019
110         act       comp          27            01/31/2019      

我试过使用MAX(test_score(，但也许可以使用MAX(test_date(？ 两者都会起作用，因为如果分数不高于最初报告的分数，学生不会报告以后日期的额外考试分数。

这是连接多个表的较大例程的一小部分，因此我不知道是否可以替换我的 JOIN。 我只是想让例程的这个小子集产生正确数量的唯一记录

SELECT
a.name_id NameID,
a.term_id TermID,
MAX(b.score) Score
FROM students a
LEFT JOIN test_score b ON a.name_id = b.name_id AND b.test_id = 'act' AND b.element_id = 'comp'
WHERE a.term_id = 'Spring'
group by b.score,a.name_id,a.term_id
order by a.name_id

没有错误消息，但上面的结果将生成 NameID 105 的两条记录：

NameID    TermID    Score
100       Spring    25
105       Spring    19
105       Spring    21
110       Spring    27

我不确定如何写这个只选择最高分数(或仅选择最近日期的分数(

感谢您的指导。

Answer 1

要选择最高分，GROUP BY 不能包含分数...

SELECT
a.name_id NameID,
a.term_id TermID,
MAX(b.score) Score
FROM students a
LEFT JOIN test_score b ON a.name_id = b.name_id AND b.test_id = 'act' AND b.element_id = 'comp'
WHERE a.term_id = 'Spring'
group by a.name_id,a.term_id
order by a.name_id

要获取与最高日期关联的分数...

SELECT x.NameID,
x.TermID,
y.score
FROM (
SELECT
a.name_id NameID,
a.term_id TermID,
---  MAX(b.score) Score
MAX(b.test_date) test_date
FROM students a
LEFT JOIN test_score b ON a.name_id = b.name_id AND b.test_id = 'act' AND b.element_id = 'comp'
WHERE a.term_id = 'Spring'
group by a.name_id,a.term_id ) x
LEFT JOIN test_score y ON x.nameid = y.name_id AND x.test_date = y.test_date
order by x.nameid