求一句SQL语句.

[zl_c]

求某校学生平均成绩在80分以上的班级的女性英语老师的基本情况(年龄等).

table1 (英语老师情况表)
teachname classid sex age
------------- --------- --------- --------
刘老师 1 女 25
王老师 3 男 27
张老师 2 女 41
......

table2 (学生成绩表)
score stuname classid
---------- ----------- ----------
85 学生A 3
75 学生B 1
100 学生C 1
88 学生D 2
92 学生E 3
......

先谢了.

[zl_c]

还要统计出平均成绩和该班级的学生人数.

[lxzm1001]

select a.* from table1 a,(select classid,avg(score) as score from table2 group by classid where score>80) b where a.classid=b.classid

[guanjm]

select a.* from table1
a
inner join
(select classid,avg(score) as score from table2 group by classid having
avg(score)>80)
b on a.classid=b.classid

[samfeng_2003]

create table t1
(teachname varchar(20),classid int,sex varchar(20),age int)

insert t1
select '刘老师',1,'女',25 union all
select '王老师',3,'男',27 union all
select '张老师',2,'女',41

create table t2
(score int,stuname varchar(20),classid int)

insert t2
select 85,'学生A',3 union all
select 75,'学生B',1 union all
select 100,'学生C',1 union all
select 88,'学生D',2 union all
select 92,'学生E',3

select a.teachname,a.classid,a.age,avg(b.score) as avg_score,count(b.stuname) as sum_stu from t1 a,t2 b
where a.classid=b.classid
group by teachname,a.classid,age
having avg(b.score)>80

drop table t1,t2

teachname classid age avg_score sum_stu
-------------------- ----------- ----------- ----------- -----------
刘老师 1 25 87 2
王老师 3 27 88 2
张老师 2 41 88 1

（所影响的行数为 3 行）

[ycy589]

select a.* from table1 a where (select avg(score) from table2 b where b.classid=a.classid group by b.classid)>=80

[gotocool]

这个应该不难

[Great6661]

select t1.teachname,t1.sex,t1.age,t3.avglevel,t3.number from t1 left join
(select avg(score) avglevel,count(*) number,classID from t2 group by classID having avg(score)>80 ) t3 on t1.classID=t3.classID