select a.id,b.name,a.score from (select id,avg(score) score from score group by id) a,student b where a.id = b.id;
all: select a.id,b.name,a.score from (select id,avg(score) as score from score group by id) a,student b where a.id = b.id; >85 select a.id,b.name,a.score from (select id,avg(score) as score from score group by id) a,student b where a.id = b.id and a.score > 85;
select f.id,f.name,e.score from student f,(select id,avg(score) as score from (select a.id,b.id,isnull(c.score,0.00) as score from student a,lesson b left join score c on a.id=c.id and b.id=c.lesson) d group by id having avg(score)>85) e where f.id=e.id order by f.id ------------------------------------------------------------------------ 楼上的弟兄们大意了。
楼上的语法有问题,改一下: ------------------------- select f.id,f.name,e.score from student f,(select id,avg(score) as score from (select a.id,b.id,isnull(c.score,0.00) as score from student a,lesson b, score c where a.id*=c.id and b.id*=c.lesson) d group by id having avg(score)>85) e where f.id=e.id order by f.id
(select id,avg(score) score from score group by id) a,student b where a.id = b.id;
select a.id,b.name,a.score from
(select id,avg(score) as score from score group by id) a,student b where a.id = b.id;
>85
select a.id,b.name,a.score from
(select id,avg(score) as score from score group by id) a,student b where a.id = b.id and a.score > 85;
from student f,(select id,avg(score) as score
from (select a.id,b.id,isnull(c.score,0.00) as score from student a,lesson b
left join score c on a.id=c.id and b.id=c.lesson) d
group by id having avg(score)>85) e
where f.id=e.id order by f.id
------------------------------------------------------------------------
楼上的弟兄们大意了。
-------------------------
select f.id,f.name,e.score
from student f,(select id,avg(score) as score
from (select a.id,b.id,isnull(c.score,0.00) as score from student a,lesson b,
score c where a.id*=c.id and b.id*=c.lesson) d
group by id having avg(score)>85) e
where f.id=e.id order by f.id