求一个简单的单表查询~~~

[桐桐-Dragon]

table-student
id age classId
1 20 2
2 18 3
求：查询
平均学生的年龄大于 本班 平均年龄的学生信息

[沉---枫]

楼主的问题看不懂啊！真心看不懂

[桐桐-Dragon]

引用 2 楼 cupid1102 的回复:

select * from stutent where age > (select avg(age) from stutent);

是的，是和本班的学生比，，，

[求知路漫漫]

你是不是查询 年龄大于 本班 平均年龄的学生信息

[cupid]

select * from stutent where age > (select avg(age) from stutent);

[求知路漫漫]

引用 2 楼 cupid1102 的回复:

select * from stutent where age > (select avg(age) from stutent);

你的年龄是每班的平均年龄吗？ 你的age是和同班的平均年龄比的吗？

[求知路漫漫]

select * from lian l where age > (select avg(age) from lian where classid = l.classid);

[求知路漫漫]

select * from stutent s where age > (select avg(age) from stutent where classid = s.classid);

[hello_cyx]

select *,avg(age)over(partition by classid) avg_age where age>avg_age
 from student;

[七朝七暮]

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);