mysql - SQL - 如何列出低于平均水平的项目

Question

我有 3 个表的基本数据库。 “学生”“考试”“分数”

对于每项考试，我需要列出所有考试成绩低于该考试平均分的学生。 (如果这有任何意义的话)

我有一个 SQL 查询，它只打印每个测试的平均分数。

SELECT t.Test_name, AVG(sc.Result) AS Avgscore
FROM Tests t 
JOIN Scores sc ON t.id_Tests = sc.Tests_id_Tests
JOIN Students s ON sc.Students_id_Students = s.id_Students
WHERE t.id_Tests = $c"

($c 是来自 for 循环的参数，它递增以将每个测试作为单独的表打印出来)

如有帮助，将不胜感激

Answer 1

更改您想要显示的任何列的选择列表，但这将根据您的需要限制结果，对于给定的 testid(将 testXYZ 替换为您正在搜索的实际测试)

SELECT t.Test_name, s.*, sc.*
  FROM Tests t
  JOIN Scores sc
    ON t.id_Tests = sc.Tests_id_Tests
  JOIN Students s
    ON sc.Students_id_Students = s.id_Students
 WHERE t.id_Tests = 'textXYZ'
   and sc.result <
       (select avg(x.result)
          from scores x
         where sc.Tests_id_Tests = x.Tests_id_Tests)

注意:要为所有测试运行此程序，并将分数限制为低于每个测试平均水平的分数，您只需将该行从 where 子句中删除并运行:

SELECT t.Test_name, s.*, sc.*
  FROM Tests t
  JOIN Scores sc
    ON t.id_Tests = sc.Tests_id_Tests
  JOIN Students s
    ON sc.Students_id_Students = s.id_Students
 WHERE sc.result <
       (select avg(x.result)
          from scores x
         where sc.Tests_id_Tests = x.Tests_id_Tests)