我现在有这样一个数据表:
记录号 教师 课程 分数 班级
分数为该班每个学生对本班各任课教师打的分。有32个班,各班任课教师人数不等
现在我想求得这样一个结果:
求各个班级每位任课教师的去掉%5的最高分和%5的最低分后的平均分,最后按班级排序
记录号 教师 课程 分数 班级
分数为该班每个学生对本班各任课教师打的分。有32个班,各班任课教师人数不等
现在我想求得这样一个结果:
求各个班级每位任课教师的去掉%5的最高分和%5的最低分后的平均分,最后按班级排序
str:='select 教师,avg(分数) as 平均分,班级 from 输入表 group by 教师,班级 order by 班级,avg(分数) desc';
四舍五入我也会了,用round即可。
但是,求各个班级每位任课教师的去掉%5的最高分和%5的最低分后的平均分
还不知道,这么长时间没人答复,求大虾们不吝惕教。
还是分开实现 用一个临时表存储满足条件的记录
对临时表再进行操作
from
(select 教师,round(max(分数)*0.95,2),班级
from 表
group by 教师,班级
union select 教师,round(min(分数)*0.95,2),班级
from 表
group by 教师,班级
union select 教师,班级,分数
from 表
where (not exists (select 教师,班级,max(分数) from 表 group by 教师,班级))
and (not exists (select 教师,班级,min(分数) from 表 group by 教师,班级))) A
group by 教师,班级
order by 班级
试试看吧!大致就这样,可能需要小的改动
A.fteacher AS 老师,
(SUM(f)-MAX(f)*AVG(highprenum)-MIN(f)*AVG(lowprenum))/(COUNT(*)-AVG(highprenum)-AVG(lowprenum)) AS 评分
FROM tscore A,
(
SELECT fclass,fteacher,
MAX(highest) AS highscore,ROUND(COUNT(highest)*0.05) AS highprenum,
MIN(lowest) AS lowscore, ROUND(COUNT(lowest)*0.05) AS lowprenum
FROM
(
SELECT fclass,fteacher,f AS highest,NULL AS lowest FROM tscore A
WHERE f=(SELECT MAX(f) FROM tscore WHERE fclass=A.fclass
AND fteacher=A.fteacher)
UNION ALL
SELECT fclass,fteacher,NULL AS highest,f AS lowest FROM tscore A
WHERE f=(SELECT MIN(f) FROM tscore WHERE fclass=A.fclass
AND fteacher=A.fteacher)
)A GROUP BY fclass,fteacher
)B WHERE A.fclass=B.fclass AND A.fteacher=B.fteacher GROUP BY A.fclass,A.fteacher