访问sql平均三列 [英] access sql average three columns

问题描述

我有下表:

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)

Table 3
Exam ID, Teacher's review
(1,3)
(2,5)
(3,4)
(4,2)

Table 4
Exam_ID, Start_Time, End_Time
(1,2:00:00,3:50:00)
(2,2:00:00,4:00:00)
(3,3:00:00,5:20:00)
(4,3:00:00,5:00:00)

每门考试都由成对的学生来解决...例如，我希望能够平均每对学生参加的所有考试的分数，老师的复习成绩:考试2和4由同一对学生进行学生(3,1)我希望能够对这两项考试(4,5)= 4.5和老师的评语(5,2)= 3.5的分数平均 然后将这些对从最高分到最低分和老师的评论进行排名，并能够计算每次考试的开始时间和结束时间之间的差异，并将其也包括在排名中 因此所需的表格如下:

each exam is solved by pairs of students ... i want to be able to average the marks, Teacher's review, 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 Teacher's review (5,2)=3.5 and then rank those pairs from highest to lowest marks and teacher's review and be able to calculate the difference between start and end time for each exam and include this in ranking as well so the required table would be like:

StudentA, StudentB, AverageMark, AverageTeacher'sReview, AverageExamDuration
( 3,       1,        4.5,           3.5,                     2:00:00)

等

相关问题: average-multiple-rows-in-a -访问中的列

谢谢

推荐答案

听起来像作业，如果是这样的话，第一次加入的！=可能就是你应该学的东西.

Sounds like homework, the != in the first join is probably what you are supposed to learn if so.

select A.StudentId as StudentA, 
B.StudentId as StudentB,
AVG(Mark) as AvgMark,
AVG(review) as AvgReview, 
AVG(DATEDIFF(MINUTE,StartTime,EndTime)) as AvgTime
from StudentToExam A 
inner join StudentToExam B on A.ExamId = B.ExamId and A.StudentId != B.StudentId
inner join ExamToMark M on A.ExamId = M.ExamId
inner join ExamToReview R on A.ExamId = R.Review
inner join ExamDuration D on D.ExamId = A.ExamId
GROUP BY A.StudentId, B.StudentId

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