请问高手:查询每行与平均数之差如何做
有下表dx:
Cname Nscore
a1 60
a2 70
a3 80
a3 90
a4 100

问查询每条记录与平均数之差的查询语句如何做?
我一开始用:
select dx.Cname,dx.Nscore-avg(dx.Nscore) from dx
结果只出来一行,如果用上group by Cname,那出来不是所要求的.请教高手如何写这个语句

------解决方案--------------------
select Nscore -(select avg(Nscore)as pin from dx ) from dx
------解决方案--------------------
select Cname, Nscore , Nscore -(select avg(Nscore) from dx) as chayi from dx
------解决方案--------------------
select Cname, Nscore , Nscore -(select avg(Nscore) from dx) as chayi from dx 
正确
------解决方案--------------------
select dx.Cname,dx.Nscore-T.avgNscore 
from dx join (select avg(Nscore)as avgNscore from dx ) T

------解决方案--------------------

SQL code

select dx.Cname, dx.Nscore-T.avgNscore  
from dx join (select avg(Nscore) as avgNscore from dx ) T