从数据库中获得平均水平以上的学生 [英] Getting the above average student from database

问题描述

我创建了一个包含以下内容的视图:

I've created a view that contains:

 student_full_name    subject_code    result
 Jennifer Higgins     CS1234          81
 Jennifer Higgins     CS1235          90
 Kal Penn             CS1234          70
 Kal Penn             CS1235          60
 Han Solo             CS1234          45
 Han Solo             CS1235          70

我正在尝试获取:

1. 每个学生的平均成绩，比如说詹妮弗·希金斯(Jennifer Higgins)参加了CS1234和CS1235. 她的平均分数将为85.50.

1. Average result of each student so like say Jennifer Higgins enrolled in CS1234 and CS1235. Her average mark would be 85.50.

然后将詹妮弗·希金斯(Jennifer Higgins)分数与所有入学人数的平均分数进行比较 因此，对所有受试者进行AVG(结果)汇总.

Then Jennifer Higgins marks would be compared to the average mark of all enrolments So totalling up the AVG(result) for all subjects.

然后查询将列出所有得分高于平均分的学生.

The query would then list all the students who are getting above average scores.

我知道我必须在这里使用子查询才能获取所有结果的AVG. 好吧，这是一种伪代码.我很困惑，因为我不确定如何使子查询将自身与查询结果进行比较. 我很确定我需要两个group by语句，一个用于按Student_full_name进行分组，另一个用于获得所有平均结果.

I know I have to use a sub query here in order to get the AVG of all results. Well this is sort of a pseudo code. I am quite stuck as I'm not sure how to make the subquery compare itself to the query's results. I'm pretty sure I need two group by statements one for the grouping by student_full_name and the other to get all of the average results.

 SELECT student_full_name,
        AVG(results) AS average_result
 FROM viewEnrol
 WHERE average_result > ( SELECT (AVG(results))
                          FROM viewEnrol

 GROUP BY student_full_name

//编辑

输出应该看起来像.卡尔·佩恩(Kal Penn)和汉·索罗(Han Solo)未列在榜单上，因为他们没有超过平均分. 所有科目的平均分是69.33.汉·索罗(Han Solo)获得57.5，卡尔·潘(Kal Penn)获得65.

OUTPUT should look like. Kal Penn and Han Solo is not listed as they didn't get above average mark. Average mark of all subjects is 69.33. Han Solo got 57.5 and Kal Penn got 65.

 student_full_name    subject_code    result
 Jennifer Higgins     CS1234          85.5

有帮助吗?

推荐答案

这应该可以完成工作.第一个内部查询将为您提供所有学生的平均值. 而第二将给出该表的平均值.

This should do the job. First inner query will give you the result of average of all students. While second will give the avg for the table.

    SELECT student_full_name 
    FROM (SELECT student_full_name,
                 AVG(results) AS average_Sresult
          FROM viewEnrol
          GROUP BY student_full_name) sre, 
         (SELECT (AVG(results)) tavg
          FROM viewEnrol) ta
     WHERE sre.average_Sresult > ta.tavg

PS:您不应该采用平均值的平均值，而不是所有结果的平均值.

PS: Should not you take the avg of avg rather than taking the avg of all results.

这篇关于从数据库中获得平均水平以上的学生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！