求一个考核教师的sql语句

[低代码开发平台]

教师考评：
有下成绩表Score
ID 自动增长，StudentID学号，TeacherID 教师ID，SubjectID科目，Score 分数
现求一sql语句得到如下结果
SubjectID ，TeacherID，教师该科绩效

教师该科绩效=所教学生的成绩前20名的平均分*40%+所教学生的全部平均分*60%

[sdhdy]

select SubjectID,TeacherID, 教师该科绩效= (select avg(score) from ((select top 20 score from Score a where a.SubjectID=Score.SubjectID and a.TeacherID=Score.TeacherID order by a.sscore desc)) c)*0.4+(select avg(score) from score d where d.SubjectID=Score.SubjectID and d.TeacherID=Score.TeacherID)*0.6
from Score group by SubjectID,TeacherID

[sdhdy]

select SubjectID,TeacherID, 教师该科绩效= (select avg(score) from ((select top 20 score from Score a where a.SubjectID=Score.SubjectID and a.TeacherID=Score.TeacherID order by a.score desc)) c)*0.4+(select avg(score) from score d where d.SubjectID=Score.SubjectID and d.TeacherID=Score.TeacherID)*0.6
from Score group by SubjectID,TeacherID order by  SubjectID,TeacherID

[lklovelk]

[Quote=引用 2 楼 sdhdy 的回复:]
SQL codeselect SubjectID,TeacherID, 教师该科绩效= (select avg(score) from ((select top 20 score from Score a where a.SubjectID=Score.SubjectID and a.TeacherID=Score.TeacherID order by a.score desc)) c)*0.4+(select avg(score) from score d where d.SubjectID=Score.SubjectID and d.TeacherID=Score.TeacherID)*0.6
from Score group by SubjectID,TeacherID order by SubjectID,TeacherID
[/Quote]
楼上正解，语句有点长，但思路并不复杂！
帮顶！

[dawugui]

请给出表结构,测试数据,相关算法和需要的结果.谢谢!