求一个简单的单表查询~~~
table-student
id age classId
1 20 2
2 18 3
求：查询
平均学生的年龄大于 本班 平均年龄的学生信息 楼主的问题看不懂啊！真心看不懂
[解决办法]
SQL> select id, age, student.classid from student, ( select avg(age) avgage, classid from student group by classid ) class where age > class.avgage and student.classid = class.classid;

表内容：
SQL> select * from student;

ID AGE CLASSID
---------- ---------- ----------
1 20 2
2 18 3
3 20 3

结果：
ID AGE CLASSID
---------- ---------- ----------
3 20 3
[解决办法]
大致思路如下：
1.先用分组函数求每个班级的平均年龄
2.全表查询班级表，然后用每个人的年龄和第1步中的平均年龄做比较

with tb_a as (
     select 1 id ,30 age ,1 classid from dual  
 
                                         union 
     select 2 id ,10 age ,3 classid from dual 
                                         union 
     select 3 id ,20 age ,1 classid from dual 
                                         union 
     select 4 id ,33 age ,2 classid from dual 
                                         union 
     select 5 id ,30 age ,1 classid from dual 
                                         union 
     select 6 id ,31 age ,2 classid from dual 
                                         union 
     select 7 id ,40 age ,3 classid from dual 
                                         union 
     select 8 id ,10 age ,2 classid from dual 
                                         union 
     select 9 id ,39 age ,1 classid from dual 

)

select *
  from tb_a c
 where exists (        
        select 1
          from (select a.classid, avg(a.age) avage
                   from tb_a a
                  group by a.classid) b        
         where c.classid = b.classid
           and c.age > b.avage);