Oracle 查询每门功课都大于各个功课平均成绩的学生

create table t(student_id number,course varchar2(10),score number);
insert into t values(1,'语文',80);
insert into t values(1,'数学',85);
insert into t values(2,'语文',90);
insert into t values(2,'数学',88);
insert into t values(3,'语文',70);
insert into t values(3,'数学',78);
insert into t values(4,'语文',60);
insert into t values(4,'数学',100);
COMMIT;

 

SQL>
SQL> SELECT c.student_id,c.course,c.score,c.avg_score FROM
2 (
3 SELECT b.student_id,b.course,b.score,b.avg_score,SUM(b.largerthan) OVER(PARTITION BY b.student_id) cnt_largerthan,COUNT(b.course) OVER(PARTITION BY b.student_id) cnt_cur FROM
4 (
5 SELECT a.student_id,a.course,a.score,a.avg_score,(CASE WHEN a.score > a.avg_score THEN 1 ELSE 0 END ) largerthan FROM
6 (
7 SELECT t.student_id,t.course,t.score,AVG(t.score) OVER(PARTITION BY t.course) avg_score FROM t
8 ) a
9 ) b
10 ) c WHERE cnt_largerthan = cnt_cur
11 ;

STUDENT_ID COURSE SCORE AVG_SCORE
---------- ---------- ---------- ----------
2 语文 90 75
2 数学 88 87.75

SQL>