sql - SQL查询在单个查询中检索不同聚合类型的平均值

Question

这是我的数据模型：

我需要获得课堂上所有学生的姓名、姓氏和个人平均票数，这些平均票数低于课堂上所有学生的平均票数。遵循所需的输出：

+---------+------+--------+-------------+---------------+
| surname | name |  class |   class_avg |   studend_avg |
+---------+------+--------+-------------+---------------+
| b       | b    |      1 |      4.1250 |        2.7500 |
+---------+------+--------+-------------+---------------+

我编写了以下在 mariadb 数据库中正常工作的查询：

SELECT student.surname, student.name, student.classroom, classroom.average AS classroom_average, AVG(vote.vote) AS student_average
    FROM (student INNER JOIN vote ON student.fiscalcode = vote.fiscalcode)
    INNER JOIN
      (select student.classroom AS classroom, AVG(vote.vote) AS average
      FROM student INNER JOIN vote ON student.fiscalcode = vote.fiscalcode
      GROUP BY student.classroom) AS classroom
    ON student.classroom=classroom.classroom
GROUP BY student.surname, student.name, student.classroom, classroom.average
HAVING AVG(vote.vote) < classroom.average;

但是我在 MS Access 中有这个错误：

您的查询不包括作为聚合函数的一部分的指定表达式“AVG(vote.vote)/AVG(vote.vote) <classic.average”

还有其他一些更简单的方法来编写这个查询吗？

标签： sqldatabasems-access

Answer 1

考虑：

SELECT StudentAvg.fiscalcode, student.name, student.surname, StudentAvg.AvgOfvote, ClassAvg.AvgOfvote
FROM ((SELECT student.classroom, student.fiscalcode, Avg(vote.vote) AS AvgOfvote
FROM student INNER JOIN vote ON student.fiscalcode = vote.fiscalcode
GROUP BY student.classroom, student.fiscalcode) As StudentAvg INNER JOIN (SELECT student.classroom, Avg(vote.vote) AS AvgOfvote
FROM vote INNER JOIN student ON vote.fiscalcode = student.fiscalcode
GROUP BY student.classroom) AS ClassAvg ON StudentAvg.classroom = ClassAvg.classroom) INNER JOIN student ON StudentAvg.fiscalcode = student.fiscalcode
WHERE (((StudentAvg.AvgOfvote)<[ClassAvg]![AvgOfvote]));