求一个简单的单表查询~~~
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);
