sql求所有分数不低于80分的学生+所有分数高于平均分的学生

假如成绩表t有学生student、科目project、成绩grade三个字段

1.求所有分数不低于80分的学生

方法1：

select student from t
where student not in (select student from t where score<80)
group by student

方法2：

select student,min(score) as min_score from t group by student having min_score>=80

 

2.求所有分数高于平均分的学生，这里平均分是所有科目所有成绩的总平均分

方法1：

select student from t
where student not in (select student from t where score<(select avg(score) from t))
group by student

方法2：可以用hive的窗口函数！想一下，所有分数高于平均分，就是学生最低的分数高于平均分

select student from
(
select student,min(score) over(partition by userid) as min_score,avg(score) over() as avg_score from t 
) x
where min_score>avg_score
group by student

 

3.假如是求所有科目分数都高于科目的平均分呢？

select student from t where student not in
(
	select student from
	(
		select student,avg(score) over(partition by project) as dangqian_project_avg_score,project,score from t 
	) x 
	where score<=dangqian_project_avg_score
) group by student

 

可以发现，窗口函数减少了not in，或者是join的次数。