sql 类似歌手得分或跳水比赛的打分规则.

表A
id , score , teacher
01   1       1
01   2       2
01   3       3
01   4       4
01   5       5
.....
02   1       1
02   2       2
02   3       3
....
....
说明,ID为学生号,score为得分,teacher为老师
要求如下,
1:如果一个ID,有九个或九个老师以上打分(有九条或九条以上记录),去掉两个最高分,两个最低分,然后剩余的取平均.
2:如果一个ID,有八个或八个老师以下打分(有八条或八条以下记录),去掉一个最高分,一个最低分,然后剩余的取平均.
3:使用一条SQL完成,不使用函数,动态SQL等. 

 

create table a
(
id varchar(2),
score int,
teacher int
)

insert into a select '01',   1,       1
insert into a select '01',   2,       2
insert into a select '01',   3,       3
insert into a select '01',   4,       4
insert into a select '01',   5,       5
insert into a select '01',   6,       6
insert into a select '01',   7,       7
insert into a select '01',   8,       8
insert into a select '02',   1,       1
insert into a select '02',   2,       2
insert into a select '02',   3,       3
insert into a select '02',   4,       4
insert into a select '02',   5,       5
insert into a select '02',   6,       6
insert into a select '02',   7,       7
insert into a select '02',   8,       8
insert into a select '02',   9,       9

--语句
select * from
(select id,(sum(score) - min(score) - max(score))*1.0/(count(1) - 2) as [平均数]
from a where a.id in (select id from a group by id having count(1) <= 8)
group by id
union
select t1.id,(sum(score) - maxscore - minscore )*1.0/(count(1) - 4) as [平均数]
from a t1 left join (select id,sum(score) as maxscore
                     from
                        (select * from a t1
                         where teacher in (select top 2 teacher from a where id = t1.id order by score desc)
                        )a
                     group by id
                    )t2 on t1.id = t2.id
          left join (select id,sum(score) as minscore
                     from
                        (select * from a t1
                         where teacher in (select top 2 teacher from a where id = t1.id order by score)
                        )a
                      group by id
                     )t3 on t1.id = t3.id
where t1.id in (select id from a group by id having count(1) >= 9)
group by t1.id,maxscore,minscore
)tt