抓取每个测试最高分的AVG分数

Question

我在写一个查询时遇到了一个问题，这个查询可以为一个学生计算三个考试分数的平均值。考虑以下TEST_SCORES表：

ID   Name   TestCode  Score
-----------------------------
119  Joe       MCA      108  
119  Joe       BRT      98
119  Joe       LPO      76
119  Joe       BRT      111
119  Joe       ALK      83
119  Joe       MCA      100
119  Joe       RTK      75

For my scenario, I only want to consider scores from the "MCA" test, 
the "BRT" test, and the "RTK" test. I need the average of those tests. 
Also, I want to take the highest grade received for those 
tests (This is where I get stuck at). The following is what I have
so far:

SELECT A.ID, avg(A.Score)
FROM TEST_SCORES A
WHERE A.TestCode in ('MCA','BRT','RTK')
AND A.ID = 119
GROUP BY A.ID

这个学生参加"BRT“考试和"MCA”考试的分数超过一项。我试着为每一次测试争取最大的测试分数。我试着使用一个条件来获取最大分数，但我总是以最高的测试成绩期结束，而不是三个测试的平均值。

如果能在这方面提供任何帮助，我们将不胜感激。提前谢谢。

Answer 1

获取每个ID的max分数，首先是testCode，然后是每个ID的avg。

SELECT ID,AVG(maxScore) as avgScore
FROM (SELECT ID,TestCode,MAX(Score) as maxScore
      FROM TEST_SCORES
      WHERE TestCode in ('MCA','BRT','RTK') 
      GROUP BY ID,TestCode
     ) t
GROUP BY ID

Answer 2

设置不是最佳的(这个名称不应该出现在这个表中，它应该在一个较小的查找表中，将一个名称与每个ID关联起来，如下图所示)。

除此之外，根据您的Oracle版本(您应该始终将其包含在您的问题中)，您可能能够也可能无法使用lateral子句(自Oracle12.1起就可用)来提供更有效的解决方案，只需一次通过数据--即使您需要所有学生的平均分数，而不仅仅是一次。

然而，另一个观察--如果一个学生根本没有参加任何一个考试(考试代码)，那么这个考试就不会在计算中被考虑，而不是在分数为0的情况下(在现实生活中通常是如此)。接受的答案不能处理这种可能性，下面的解决方案也是如此。如果必须处理，那么您需要澄清您的问题/要求。

with
     test_data ( id, testcode, score ) as (
       select 119, 'MCA', 108 from dual union all 
       select 119, 'BRT',  98 from dual union all
       select 119, 'LPO',  76 from dual union all
       select 119, 'BRT', 111 from dual union all
       select 119, 'ALK',  83 from dual union all
       select 119, 'MCA', 100 from dual union all
       select 119, 'RTK',  75 from dual union all
       select 200, 'ABC', 110 from dual union all
       select 200, 'LPO',  90 from dual union all
       select 200, 'BRT',  90 from dual union all
       select 200, 'ALK', 102 from dual union all
       select 200, 'LPO',  90 from dual
     ),
     students ( id, name ) as (
       select 119, 'Joe' from dual union all
       select 200, 'Ann' from dual
     )
select s.id, s.name, avgscore
from   students s,
       lateral ( select   avg(max(score)) as avgscore
                 from     test_data t
                 where    t.id = s.id
                   and    testcode in ('MCA','BRT','RTK')
                 group by testcode
               )
;

 ID  NAME  AVGSCORE
---  ----  --------
119  Joe         98
200  Ann         90