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