求一个考核教师的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

[yjkarmon]

SELECT C.SubjectID ，C.TeacherID, 教师该科绩效=b.FAvgScore*0.4+A.FAvgScore1*.06
FROM tbl1 C
INNER JOIN
(
SELECT TOP 20
FROM
(
SELECT TeacherID,SubjectID,avg(Score) AS FAvgScore FROM Tbl1
GROUP BY TeacherID,SubjectID
) A
ORDER BY FAvgScroe DESC
) B ON A.TeacherID=B.A.TeacherID AND A.SubjectID=B.SubjectID
INNER JOIN
(
SELECT TeacherID,SubjectID,avg(Score) AS FAvgScore1 FROM Tbl1
GROUP BY TeacherID,SubjectID
) A ON A.TeacherID=C.A.TeacherID AND A.SubjectID=C.SubjectID

[arrow_gx]

来晚了

富农 sdhdy 大江东去... 正解，

[ljluck7687]

mark