表内容:
SQL> select * from grade_tab;
NAME CLASSGRADE
-------- ----- ----------
A1
60
B1
70
C1
80
D1
90
E1
95
F2
65
G2
70
H2
75
I2
80
J2
85
10 rows selected
脚本一:
select a.name, a.grade, a.class, b.avg_grade
from grade_tab a,
(select class, avg(grade) avg_grade from grade_tab group by class) b
where a.class = b.class
anda.grade > b.avg_grade;
结果:
NAME
GRADE CLAVG_GRADE
-------- ---------- -- ----------
C
80 1
79
D
90 1
79
E
95 1
79
I
80 2
75
J
85 2
75
脚本二:
select a.name, a.grade, a.class from grade_tab a, grade_tab b
where a.grade > (select avg(grade) from grade_tab where a.grade = b.grade);
结果:
NAME
GRADE CL
-------- ---------- --
C
801
I
80 2
D
901
E
951
C
801
I
80 2
J
85 2
脚本一的结果是对的, 脚本二的结果有重复。
为什么脚本二的结果有重复呀? 请指教!
|