在 Access 中对列中的多行进行平均

Question

我有下表：

Table 1
Student , Exam_ID
1      1      
2      1     
3      2     
1      2     
3      3     
2      3     
3      4     
1      4  

Table 2
Exam ID, Mark
(1   , 5)
(2 ,   4)
(3  ,  4)
(4 ,   5)

每项考试都是由成对的学生解决的...我希望能够平均每对学生参加的所有考试的分数，例如：考试 2 和 4 由同一对学生参加(3,1) 我希望能够计算这两次考试的平均分数，即(4,5)=4.5 然后将这些对从最高分到最低分进行排名 谢谢

如何将名字和姓氏包含到第一个表中？

Answer 1

SELECT
    a.Student AS studentA
  , b.Student AS studentB
  , AVG(T2.Mark) AS averageMark
FROM ( T2
      INNER JOIN T1 AS a
          ON a.Exam_ID = T2.Exam_ID
     )  
      INNER JOIN T1 AS b
          ON a.Exam_ID = b.Exam_ID
          AND a.Student < b.Student
GROUP BY a.Student
       , b.Student
ORDER BY AVG(T2.Mark) DESC

或者这个：

SELECT
    a.Student AS studentA
  , b.Student AS studentB
  , AVG(T2.Mark) AS averageMark
FROM ( T1 AS a
      INNER JOIN T1 AS b
          ON a.Exam_ID = b.Exam_ID
          AND a.Student < b.Student
     )
      INNER JOIN T2
          ON a.Exam_ID = T2.Exam_ID
GROUP BY a.Student
       , b.Student
ORDER BY AVG(T2.Mark) DESC

它的工作原理在这里更明显。括号内的 JOIN 找到配对，下一个 JOIN 将配对与第二个 Marks 表关联起来。