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.75SQL>
转载于:https://www.cnblogs.com/Uncho/p/4056255.html
