题解 | 两种解法 |个人笔记

1 开窗聚合函数

知识点：开窗聚合函数——将聚合函数 sum() count() avg() max() min() 与聚合函数 over() 连用，可以动态计算在指定窗口内的各种聚合函数值。

思路：将需要的字段和每个学校平均绩点查询出来建立为新表，再从新表中取出 device_id, university 字段，并筛选出绩点大于该校平均绩点，且该校平均绩点大于3.6符合题意的数据，最后进行排序。

select device_id, university
from 
  (
    select device_id, 
           university, 
           gpa,
           avg(gpa)over(partition by university) avg_gpa
    from user_profile
    group by university, gpa, device_id
  ) a
where gpa > avg_gpa and avg_gpa > 3.6
order by device_id desc

2 join 连接

思路：先查询出每个学校的平均绩点并且平均绩点大于3.6，再将该表连接到原表上，使原表中的gpa去跟每个学校的平均绩点avg_gpa作比较，筛选出绩点大于该校平均绩点的数据，最后排序。

select a.device_id, a.university
from user_profile a
  left join (
    select university, avg(gpa) avg_gpa
    from user_profile
    group by university
    having avg(gpa) > 3.6
  ) b on a.university = b.university
where a.gpa > b.avg_gpa
order by device_id desc