avg 疑问

Question

ID     NAME    SCORE001    张       70
001    张       80             //平均    200/3=66.6
001    张       50
----------------------------------------------------------------
002    李       60             //平均         75
002    李       90
-----------------------------------------------------------------
003    赵       100
003    赵       60             //平均        76.6      
003    赵       70
对id 分组 ,要求得到比各自组的平均score大的记录 
select *
from tb a
where score>(select avg(score) score from tb where id=a.id)帮我讲解一下这条SQL语句,为什么利用嵌套就可以实现了?
为什么avg 可以不用接group by 呢, 一般avg 不是一定要跟group by 
一起使用的? 

Answer 1

group by是用在select 里有非聚合统计函数时
当列里只有聚合函数（不用分组统计）时，不要group by 

Answer 2

select avg(score) score from tb 
比如说这句，这样写就是对全体数据avg，当然不用写group by 
不过你这句语句效率不高
可以换成这样试试看
select * 
from tb a 
where score> avg(score) over (partition by id )

Answer 3

select avg(score) score from tb where id=a.id
这时面之所以没有group by 
因为先进行了数据过滤，出来的都是同一id
相当于对整体数据求avg了
只要是整体求sum,min,max,avg之类都不需要group by

Answer 4

我如果象下面这样写, select *
from student
a,
(select id, avg(score) score from student group by id) b
where a.id = b.id
and a.score > b.score;
跟
select *
from tb a
where score>(select avg(score) score from tb where id=a.id) 相比,哪一条效率会高一些呢

Answer 5

select * 
from student 
a, 
(select id, avg(score) score from student group by id) b 
where a.id = b.id 
and a.score > b.score;   ----这个执行计划多出 分组，全表扫描1次，成本肯定要高
跟 
select * 
from tb a 
where score>(select avg(score) score from tb where id=a.id)  select *  
from tb a  
where score> avg(score) over (partition by id ) --效率最高