select 表1.学生姓名,
Avg(表1.语文成绩+表1.数学成绩) AS 总的平均成绩,
Avg(表1.语文成绩) AS 语文成绩,
Avg(表1.数学成绩) AS 数学成绩,
表2.任课老师姓名,
iif(Avg(表1.语文成绩+表1.数学成绩)>=90 and Avg(表1.语文成绩+表1.数学成绩)<100,"优",iif(Avg(表1.语文成绩+表1.数学成绩)>=80 and Avg(表1.语文成绩+表1.数学成绩)<90,"良",iif(Avg(表1.语文成绩+表1.数学成绩)>=70 and Avg(表1.语文成绩+表1.数学成绩)<80,"中",iif(Avg(表1.语文成绩+表1.数学成绩)>=60 and Avg(表1.语文成绩+表1.数学成绩)<70,"及格","不及格"))))
from 表1 inner join 表2 on 表1.任课老师编号=表2.老师编号
group by 表1.学生姓名,表2.任课老师姓名
order by Avg(表1.语文成绩+表1.数学成绩)