本人觉得很难的一句sql

Question

貌似要用一句SQL写出来真的很难喔！！！

Answer 1

人   所属科室
a      科室1
b      科室1
c     科室1
d      科室2
e      科室2
f     科室2
g      科室2
h      科室2
i      科室2
j      科室2
k      科室2

Answer 2

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([评价人] varchar(1),[被评价人] varchar(1),[评分] dec(8,1),[所属科室] varchar(5))
insert [tb]
select 'a','b',3,'科室1' union all
select 'a','c',3,'科室1' union all
select 'c','a',2,'科室1' union all
select 'c','b',1,'科室1' union all
select 'b','a',1,'科室1' union all
select 'b','c',1,'科室1' union all
select 'd','e',4,'科室2' union all
select 'd','f',4,'科室2' union all
select 'd','g',4,'科室2' union all
select 'd','i',4,'科室2' union all
select 'd','j',4,'科室2' union all
select 'd','k',4,'科室2' union all
select 'e','d',4,'科室2' union all
select 'e','f',4,'科室2' union all
select 'e','i',4,'科室2' union all
select 'e','j',4,'科室2' union all
select 'e','k',4,'科室2' union all
select 'e','g',4,'科室2'
go
--select * from [tb]select  被评价人
,评分=case 
when 科室人数<6 then avg(评分) 
when 科室人数>15 then 
(select sum(评分) from tb 
where 被评价人=t.被评价人 
and 评价人 not in (select top 2 评价人 from tb where 被评价人=t.被评价人 order by 评分)
and 评价人 not in (select top 2 评价人 from tb where 被评价人=t.被评价人 order by 评分 desc))/(科室人数-5)
else
(select sum(评分) from tb 
where 被评价人=t.被评价人 
and 评价人 not in (select top 1 评价人 from tb where 被评价人=t.被评价人 order by 评分)
and 评价人 not in (select top 1 评价人 from tb where 被评价人=t.被评价人 order by 评分 desc))/(科室人数-3)
end
from 
(
select a.被评价人,a.评分,b.科室人数
from tb a join (select 所属科室,科室人数=count(distinct 被评价人) from tb group by 所属科室) b
on a.所属科室=b.所属科室
) t
group by 被评价人,科室人数
/*
被评价人 评分
---- -----------
a    1.500000
b    2.000000
c    2.000000
d    NULL
e    NULL
f    0.800000
g    0.800000
i    0.800000
j    0.800000
k    0.800000(10 行受影响)
*/因为给出的示例数据不完整，所以结果也不完整。但逻辑应该没问题。

Answer 3

结果贴错了，是这个：
/*
被评价人 评分
---- ---------------------------------------
a    1.500000
b    2.000000
c    2.000000
d    NULL
e    NULL
f    1.000000
g    1.000000
i    1.000000
j    1.000000
k    1.000000(10 行受影响)
*/