mysql - 按主题分组并按有子句聚合

Question

表标记

Student_Name   Subject   Marks
      S1             English   75
      S2             English   45
      S1             Maths     90
      S3             Maths     95
      S2             Maths     40
      S3             English   85
      S1             Science   90
      S3             Science   99
      S2             Science   42

如何查询谁拥有 >= 90% 的聚合并显示主题标记如下

Student_Name   Subject   Marks
      S3        Maths      95
      S3        English    85
      S3        Science    99

标签： mysql

Answer 1

SELECT m.*
FROM Marks m
NATURAL JOIN ( SELECT Student_Name
               FROM Marks
               GROUP BY Student_Name
               HAVING AVG(Marks) >= 90 ) subquery

小提琴