Oracle查询成绩高于成绩,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>

原文：http://www.cnblogs.com/Uncho/p/4056255.html

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！