在Access中的一列中平均多行 [英] AVERAGE multiple rows in a column in Access

问题描述

我有下表:

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的2项考试取平均分 然后将这些对从最高分到最低分 谢谢

each exam is solved by pairs of students ... i want to be able to average the mark of all exams taken by each pair of student for example : Exams 2 and 4 are taken by the same pair of students (3,1) i want to be able to average the marks for those 2 exams which are(4,5)=4.5 and then rank those pairs from highest to lowest marks thank you

如何在第一个表中包括名字和姓氏?

推荐答案

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表关联.

where it is more obvious how it works. The JOIN inside the parenthesis finds the couples and next JOIN relates the couples to the second Marks table.

这篇关于在Access中的一列中平均多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！