mySQL 所有最大值的平均值

Question

我有这样的表分数。

然后我使用查询来获取最大或最高平均值，

    SELECT stud_num, assess_type, MAX(average) FROM score 
    WHERE assess_type = "general" OR assess_type = "professional" 
    OR assess_type = "specialization"
    GROUP BY stud_num, assess_type;

结果是，

但我想添加每个 ID 的每个最大值并获取平均值。例如学生#1 = 70 +80/3，学生#​​2 = 62.5 + 76 + 72/3 等。可以在一个查询中完成此操作吗？

期望输出为

stud_num | average
1           50      //avg of general, prof, special
2           71.5
3           72.16

Answer 1

这一定能解决你的问题。尝试一下:

SELECT stud_num
     , (SUM(max_average)/3) as final_average
     , access_type 
  FROM 
     ( SELECT stud_num
            , access_type
            , MAX(average) as max_average 
         from score 
        where access_type IN ('general', 'professional', 'specialization') 
        GROUP 
           BY stud_name
            , access_type
      ) as temp_score 
  GROUP 
     BY stud_num