请问如下问题的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等.
问题点数：100、回复次数：69Top
1 楼caixia615（＊^_^＊)‵My ɡīr!.ˊ想念妳ˋ 。(＊^_^＊）回复于 2006-12-14 11:34:30 得分 0
坐沙发学习Top
2 楼crazyflower（小狂花，学习中……）回复于 2006-12-14 11:35:19 得分 0
老乌龟要求好高
3:使用一条SQL完成,不使用函数,动态SQL等.Top
3 楼coolingpipe（冷箫轻笛）回复于 2006-12-14 11:36:03 得分 0
哦，老乌龟的帖子阿！
先markTop
4 楼coolingpipe（冷箫轻笛）回复于 2006-12-14 11:36:53 得分 0
union 算不算两条？Top
5 楼leo_lesley（leo）回复于 2006-12-14 11:37:06 得分 0
学习 同时接点分Top
6 楼dawugui（潇洒老乌龟）回复于 2006-12-14 11:37:19 得分 0
union 可以考虑.Top
7 楼yczealot（）回复于 2006-12-14 11:37:49 得分 0
markTop
8 楼tangqijun199（撒旦.冲上了5角还差一个猩猩，继续努力……）回复于 2006-12-14 11:37:57 得分 0
好象比较简单饿。关键是如果只有1个老师或2个老师，去掉最高最低分不就没有了，这种情况怎么处理？Top
9 楼xiaoku（野蛮人(^v^)）回复于 2006-12-14 11:39:26 得分 0
哈哈...老师要考验学生了Top
10 楼dawugui（潇洒老乌龟）回复于 2006-12-14 11:40:16 得分 0
好象比较简单饿。关键是如果只有1个老师或2个老师，去掉最高最低分不就没有了，这种情况怎么处理？
这个暂时不考虑他.
本来还有个条件就是如果只有1个老师或2个老师,则直接取平均.Top
11 楼marco08（天道酬勤）回复于 2006-12-14 11:40:32 得分 0
聽課Top
12 楼xiaoku（野蛮人(^v^)）回复于 2006-12-14 11:41:30 得分 0
tangqijun199(撒旦.冲上了5角还差一个猩猩，继续努力……) ( ) 信誉：100 Blog 2006-12-14 11:37:57 得分: 0
好象比较简单饿。关键是如果只有1个老师或2个老师，去掉最高最低分不就没有了，这种情况怎么处理？
-----------------
而且:会曾在最高的两个老师的分是一样的,到底去两个老师呢?还是一个Top
13 楼smaworm（新白娘子玩传奇）回复于 2006-12-14 11:48:03 得分 0
学习
Top
14 楼xiaoku（野蛮人(^v^)）回复于 2006-12-14 11:49:09 得分 0
吃饭先Top
15 楼dawugui（潇洒老乌龟）回复于 2006-12-14 11:50:41 得分 0
再说明以下,如果出现同分,一样去了两个.例如
01 , 1 , 1
01 , 1 , 2
01 , 2 , 3
01 , 3 , 4
01 , 4 , 5
01 , 5 , 6
01 , 6 , 7
01 , 7 , 8
01 , 7 , 9
则去掉两个1分和两个7分的,2,3,4,5,6取平均.
类似歌手得分或跳水比赛的打分规则.
如果有3个最高分或3个最低分,则去掉其中的两个即可.Top
16 楼enutter（The Notorious G.W.W.）回复于 2006-12-14 12:05:12 得分 0
学习
Top
17 楼andy1995（发现一个不错的SQLServer网站：www.windbi.com）回复于 2006-12-14 12:42:51 得分 0
--产生测试数据
declare @d table(id varchar(10), score int, teacher int)
insert into @d select '01', 1, 1
insert into @d select '01', 2, 2
insert into @d select '01', 3, 3
insert into @d select '01', 4, 4
insert into @d select '01', 5, 5
insert into @d select '01', 6, 6
insert into @d select '01', 7, 7
insert into @d select '01', 8, 8
insert into @d select '01', 9, 9
insert into @d select '02', 1, 1
insert into @d select '02', 2, 2
insert into @d select '02', 3, 3
--解决方法
select id,avg(score) from @d a where (select count(*) from @d b where a.id=b.id)>=9
and score not in(select top 2 score from @d e where a.id=e.id order by score)
and score not in(select top 2 score from @d e where a.id=e.id order by score desc)
or
(select count(*) from @d b where a.id=b.id)<9
and score not in(select top 1 score from @d e where a.id=e.id order by score)
and score not in(select top 1 score from @d e where a.id=e.id order by score desc)
group by idTop
18 楼coolingpipe（冷箫轻笛）回复于 2006-12-14 12:48:05 得分 20
--环境
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
--结果
id 平均数
---- --------------------------
01 4.500000000000
02 5.000000000000
（所影响的行数为 2 行）
PS：没有考虑打分老师少于2个的情况Top
19 楼coolingpipe（冷箫轻笛）回复于 2006-12-14 12:52:33 得分 0
--特殊情况测试：
delete from a
insert into a select '01', 2, 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', 7, 8
insert into a select '02', 7, 9 --三个最大值
--结果：
01 4.500000000000
02 5.000000000000
--成功！！！
Top
20 楼tangqijun199（撒旦.冲上了5角还差一个猩猩，继续努力……）回复于 2006-12-14 13:03:12 得分 0
刚刚吃饭去了，来晚了，呵呵
Select M.id,(Sum(score)-Max(score)-Min(score))/(Count(1)-2) from TableName M
Left join(
Select A.id,A.teacher from TableName A
Where Not Exists(Select 1 from TableName Where A.Id=id And A.score<score)
And id in (select id from tableName group by Id having Count(1)>8)
Group by A.id,A.teacher
Union
Select A.id,A.teacher from TableName A
Where Not Exists(Select 1 from TableName Where A.Id=id And A.score>score)
And Id in (select id from tableName group by Id having Count(1)>8)
Group by A.id,A.teacher
) N
On M.id=N.id And M.teacher = N.teacher
Where N.Id Is NULL
Group By M.idTop
21 楼andy1995（发现一个不错的SQLServer网站：www.windbi.com）回复于 2006-12-14 13:12:12 得分 0
declare @d table(id varchar(10), score int, teacher int)
insert into @d select '01', 1, 1
insert into @d select '01', 2, 2
insert into @d select '01', 3, 3
insert into @d select '01', 4, 4
insert into @d select '01', 5, 5
insert into @d select '01', 6, 6
insert into @d select '01', 7, 7
insert into @d select '01', 8, 8
insert into @d select '01', 9, 9
insert into @d select '01', 9, 10
insert into @d select '01', 9, 11
insert into @d select '02', 1, 1
insert into @d select '02', 2, 2
insert into @d select '02', 3, 3
insert into @d select '02', 3, 4
insert into @d select '02', 3, 5
insert into @d select '02', 3, 6
--这里添加上如果有3个相同的最高分或者最低分的情况
select id,avg(score*1.0)
from @d a where (select count(*) from @d b where a.id=b.id)>=9
and teacher+cast(score as varchar) not in(select top 2 teacher+cast(score as varchar) from @d e where a.id=e.id order by score)
and teacher+cast(score as varchar) not in(select top 2 teacher+cast(score as varchar) from @d e where a.id=e.id order by score desc)
or
(select count(*) from @d b where a.id=b.id)<9
and teacher+cast(score as varchar) not in(select top 1 teacher+cast(score as varchar) from @d e where a.id=e.id order by score)
and teacher+cast(score as varchar) not in(select top 1 teacher+cast(score as varchar) from @d e where a.id=e.id order by score desc)
group by id
order by 1Top
22 楼xiaoku（野蛮人(^v^)）回复于 2006-12-14 13:23:02 得分 80
select t.id ,avg(score*1.0) as sc
from a t
join (select id,count(1) as c from a group by id) b on t.id =b.id
where
(b.c >=9
and t.teacher not in (select top 2 teacher from a where id =t.id order by score desc)
and t.teacher not in (select top 2 teacher from a where id =t.id order by score asc)
)
or
(
b.c >=2 and b.c<=8
and t.teacher not in (select top 1 teacher from a where id =t.id order by score desc)
and t.teacher not in (select top 1 teacher from a where id =t.id order by score asc)
)
group by t.id
id sc
---------- ----------------------------------------
01 4.500000
02 5.000000
（所影响的行数为 2 行）
Top
23 楼xiaoku（野蛮人(^v^)）回复于 2006-12-14 13:23:02 得分 0
select t.id ,avg(score*1.0) as sc
from a t
join (select id,count(1) as c from a group by id) b on t.id =b.id
where
(b.c >=9
and t.teacher not in (select top 2 teacher from a where id =t.id order by score desc)
and t.teacher not in (select top 2 teacher from a where id =t.id order by score asc)
)
or
(
b.c >=2 and b.c<=8
and t.teacher not in (select top 1 teacher from a where id =t.id order by score desc)
and t.teacher not in (select top 1 teacher from a where id =t.id order by score asc)
)
group by t.id
id sc
---------- ----------------------------------------
01 4.500000
02 5.000000
（所影响的行数为 2 行）
Top
24 楼xiaoku（野蛮人(^v^)）回复于 2006-12-14 13:27:32 得分 0
create table a
(
id varchar(10),
score int,
teacher int
)
insert into a select '01', 1, 1
insert into a select '01', 1, 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', 2, 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', 7, 8
insert into a select '02', 7, 9
insert into a select '03', 1, 1
insert into a select '03', 2, 2
insert into a select '03', 2, 3
insert into a select '04', 1, 1
insert into a select '04', 1, 2
--drop table a
--经测试 成功
select t.id ,avg(score*1.0) as sc
from a t
join (select id,count(1) as c from a group by id) b on t.id =b.id
where
(b.c >=9
and t.teacher not in (select top 2 teacher from a where id =t.id order by score desc)
and t.teacher not in (select top 2 teacher from a where id =t.id order by score asc)
)
or
(
b.c >=2 and b.c<=8
and t.teacher not in (select top 1 teacher from a where id =t.id order by score desc)
and t.teacher not in (select top 1 teacher from a where id =t.id order by score asc)
)
group by t.id
id sc
---------- ----------------------------------------
01 4.333333
02 4.800000
03 2.000000
04 1.000000
（所影响的行数为 4 行）Top
25 楼DragonBill（用户中文昵称------静观其变，以静制动）回复于 2006-12-14 13:32:12 得分 0
create table A(id varchar(2), score int, teacher varchar(2))
go
insert into A values('01',1,1)
insert into A values('01',2,2)
insert into A values('01',3,3)
insert into A values('01',4,4)
insert into A values('01',5,5)
insert into A values('01',6,6)
insert into A values('01',7,7)
insert into A values('01',8,8)
insert into A values('01',9,9)
insert into A values('02',1,1)
insert into A values('02',2,2)
insert into A values('02',3,3)
insert into A values('02',4,4)
insert into A values('02',5,5)
insert into A values('02',6,6)
insert into A values('02',7,7)
insert into A values('02',8,8)
go
select id, case when (select count(id) from A where id = a.id) > 8
then (select (sum(score) - min(score) - (select min(score)
from A where id = a.id
and score > min(b.score))
- max(score) - (select max(score)
from A where id = a.id
and score < max(b.score)))/count(a.id)
from A b where b.id = a.id)
else (select (sum(score) - min(score) - max(score))/count(a.id)
from A where id = a.id)
end
from A a group by id
go
drop table A
go
Top
26 楼xiaoku（野蛮人(^v^)）回复于 2006-12-14 13:43:07 得分 0
--经测试 成功
select t.id ,avg(score*1.0) as sc
from a t
join (select id,count(1) as c from a group by id) b on t.id =b.id
where
(b.c >=9
and t.teacher not in (select top 2 teacher from a where id =t.id order by score desc)
and t.teacher not in (select top 2 teacher from a where id =t.id order by score asc)
)
or
(
b.c >2 and b.c<=8
and t.teacher not in (select top 1 teacher from a where id =t.id order by score desc)
and t.teacher not in (select top 1 teacher from a where id =t.id order by score asc)
)
or
(
b.c =2
)
group by t.id
id sc
---------- ----------------------------------------
01 4.333333
02 4.800000
03 2.000000
04 2.000000
（所影响的行数为 4 行）Top
27 楼atouzi（）回复于 2006-12-14 13:49:09 得分 0
那个重复的最大最小怎么只取一个去掉?Top
28 楼duml789（好的）回复于 2006-12-14 13:55:45 得分 0
牛人真多Top
29 楼cnhzlyg（蓝色代码）回复于 2006-12-14 14:01:28 得分 0
GOOD 学习了
UPTop
30 楼ldw701（★水若寒★）回复于 2006-12-14 15:25:47 得分 0
mark，强贴Top
31 楼dawugui（潇洒老乌龟）回复于 2006-12-14 15:28:18 得分 0
谢谢楼上的各位兄弟姐妹.
我先逐个测试一下.Top
32 楼unicomtalkweb（）回复于 2006-12-14 16:10:15 得分 0
要是只有三条记录，两条最高分，一条最低分，都去掉了！Top
33 楼dawugui（潇洒老乌龟）回复于 2006-12-14 16:17:49 得分 0
楼上兄弟,你没看懂
少于九的只去掉一个最高分,一个最低分.Top
34 楼yudi010（★★逍遥浪子★★）回复于 2006-12-14 16:20:51 得分 0
楼上正解Top
35 楼ken2002（尖刀）回复于 2006-12-14 16:34:01 得分 0
只有学习的份呀Top
36 楼DragonBill（用户中文昵称------静观其变，以静制动）回复于 2006-12-14 16:52:49 得分 0
--考虑了一下，开始的SQL写得不完全，
--Bug在于:若存在两笔或多笔相同的最低分或最高分时，会计算错误，再把SQL改一下，
--加个判断
select id, (SumScore - FstMin - SecMin - FstMax - SecMax) / CntID as avg_score
from
(select aa.id, min(aa.score) FstMin,
--打分记录多于八笔，减两笔最低分
--否则减0
case when (count(aa.id) > 8) then
--若有多笔相同的最低分，再减一个最低分就是了
--若不存在相同的最低分，则取次低分
case when (select count(id) from A where id = aa.id and score = min(aa.score)) > 1
then min(aa.score)
else (select min(score) from A where id = aa.id and score > min(aa.score))
end
else 0
end as SecMin,
max(aa.score) FstMax,
--同上
case when (count(aa.id) > 8) then
--若有多笔相同的最高分，再减一个最高分就是了
--若不存在相同的最高分，则取次高分
case when (select count(id) from A where id = aa.id and score = max(aa.score)) > 1
then max(aa.score)
else (select max(score) from A where id = aa.id and score < max(aa.score))
end
else 0
end as SecMax,
sum(aa.score) SumScore,
count(aa.id) CntID
from A aa
group by aa.id) aTop
37 楼DragonBill（用户中文昵称------静观其变，以静制动）回复于 2006-12-14 16:54:55 得分 0
--惭愧，还是反应太慢了，判断相同笔数最低分或最高分的方式...唉...
--新SQL,减少两个子查询
select id, (SumScore - FstMin - SecMin - FstMax - SecMax) / CntID as avg_score
from
(select aa.id, min(aa.score) FstMin,
case when (count(aa.id) > 8) then
--总分数 与 扣去一个最低分后的总分数 相减, 其差竟然大于最低分
--表明最低分记录不止一笔，那么取最低分，否则取次低分
(select case when (sum(aa.score) - sum(score)) > min(aa.score) then min(aa.score)
else min(score)
end
from A where id = aa.id and score > min(aa.score))
else 0
end as SecMin,
max(aa.score) FstMax,
case when (count(aa.id) > 8) then
--总分数 与 扣去一个最高分后的总分数 相减, 其差竟然大于最高分
--表明最高分记录不止一笔，那么取最高分，否则取次高分
(select case when (sum(aa.score) - sum(score)) > max(aa.score) then max(aa.score)
else max(score)
end
from A where id = aa.id and score < max(aa.score))
else 0
end as SecMax,
sum(aa.score) SumScore,
count(aa.id) CntID
from A aa
group by aa.id) aTop
38 楼hinco（桃色德鲁依）回复于 2006-12-14 17:28:48 得分 0
declare @d table(id varchar(10), score float, teacher int)
insert into @d
select '01', 1, 1
union all select '01', 1, 2
union all select '01', 1, 3
union all select '01', 2, 4
union all select '01', 2, 5
union all select '01', 3, 6
union all select '01', 4, 7
union all select '01', 6, 8
union all select '01', 3, 9
union all select '01', 4, 10
union all select '01', 4, 11
union all select '01', 6, 12
union all select '01', 10, 13
union all select '01', 10, 14
union all select '02', 5, 1
union all select '02', 5, 2
union all select '02', 4, 3
union all select '02', 3, 4
union all select '02', 1, 5
union all select '02', 5, 6
union all select '02', 5, 7
union all select '03', 1, 1
union all select '03', 2, 2
select id,
(case when count(*)<3 then
(select avg(score) from @d where id=a.id)
when count(*)<9 then
(select avg(score) from @d b where id=a.id and
(select count(*) from @d where id=a.id and
(score>b.score or (score=b.score and teacher>b.teacher)))>0 and
(select count(*) from @d where id=a.id and
(score<b.score or (score=b.score and teacher>b.teacher)))>0)
else
(select avg(score) from @d c where id=a.id and
(select count(*) from @d where id=a.id and
(score>c.score or (score=c.score and teacher>c.teacher)))>1 and
(select count(*) from @d where id=a.id and
(score<c.score or (score=c.score and teacher>c.teacher)))>1)
end) as score
from @d a group by id
id score
---------- -----------------------------------------------------
01 3.5
02 4.4000000000000004
03 1.5Top
39 楼jackeyabc（过些天跟布什二女儿订婚)）回复于 2006-12-15 10:31:01 得分 0
帮乌龟大师顶~Top
40 楼Josion（游胆孤侠）回复于 2006-12-15 11:20:35 得分 0
学习Top
41 楼wendyc（新的每日C）回复于 2006-12-15 11:57:48 得分 0
记号 吃饭先!!Top
42 楼liuxiaolin21（）回复于 2006-12-15 14:00:18 得分 0
学习Top
43 楼shiliangdong（Stou）回复于 2006-12-15 14:19:22 得分 0
MARKTop
44 楼BEN_717_GG（修改你在CSDN社区的信息）回复于 2006-12-15 15:00:45 得分 0
学习ingTop
45 楼prcgolf（小鸟）回复于 2006-12-15 15:20:44 得分 0
upTop
46 楼yuanlfrj（codefan）回复于 2006-12-15 16:39:01 得分 0
--------------------
测试环境
if object_id('test') is not null
drop table test
create table test (id int, score int, teacher int)
insert test select 1,1,1
union all select 1,2,2
union all select 1,3,3
union all select 1,4,4
union all select 1,7,5
union all select 1,8,6
union all select 1,9,7
union all select 1,12,8
union all select 1,18,9
union all select 1,18,10
union all select 2,1,1
union all select 2,3,2
union all select 2,4,3
union all select 2,6,4
union all select 2,7,5
union all select 2,8,6
union all select 2,9,7
union all select 2,12,8
union all select 2,15,9
union all select 2,13,10
union all select 2,10,11
union all select 2,13,12
union all select 2,15,13
union all select 2,14,14
union all select 3,12,1
union all select 3,13,3
union all select 3,14,3
union all select 3,14,4
-----------
具体代码
select id,
case when (select count(*) from test where test.id = t.id) >= 9 then (select sum(score)/(select cast(count(*) as float) from test where test.id = t.id) from test
where score not in
(
select score from
(
select * from
(
select distinct top 2 id,score
from test
where test.id = t.id
order by score asc
)t
--
union all
--
select * from
(
select distinct top 2 id,score
from test
where test.id = t.id
order by score desc
)t
)t
)
and test.id = t.id
)
when (select count(*) from test where test.id = t.id) >= 8 then (select sum(score)/(select cast(count(*) as float) from test where test.id = t.id) from test
where score not in
(
select score from
(
select * from
(
select distinct top 1 id,score
from test
where test.id = t.id
order by score asc
)t
--
union all
--
select * from
(
select distinct top 1 id,score
from test
where test.id = t.id
order by score desc
)t
)t
)
and test.id = t.id)
else sum(score)/(select cast(count(*) as float) from test where test.id = t.id) end score
from test t
group by id
------------------
结果
id score
1 3.1000000000000001
2 5.8571428571428568
3 13.25
不知是否正确,请赐教?
Top
47 楼b1688890284（波波）回复于 2006-12-15 16:50:43 得分 0
眼花了﹗ UP!!!!!!!!!!!!!!!!!Top
48 楼yunbaixingxing（）回复于 2006-12-15 16:57:28 得分 0
马克
强人Top
49 楼simaxineryang（）回复于 2006-12-15 17:15:26 得分 0
牛人真多,一条sql写出来,头都要缴晕.我几条sql写的都写了一会啊.Top
50 楼podianliangshui（别问是劫是缘）回复于 2006-12-15 18:16:34 得分 0
理论上可以写出来，可是，感觉无意义，玩玩可以，实际项目中，如果有人这样一条语句完成这个任务，那一定是第一个被开除的人，原因很简单，可读性太差
其实大家把算法搞清就可以了
Top
51 楼yanzimywife_2005（准备起飞）回复于 2006-12-15 19:59:24 得分 0
学习!Top
52 楼sjjf（水晶剑锋）回复于 2006-12-16 21:39:23 得分 0
markTop
53 楼icegunner（）回复于 2006-12-16 21:56:01 得分 0
MARKTop
54 楼lvpeiyuan2（）回复于 2006-12-17 09:23:11 得分 0
markkkkkkTop
55 楼studenthj（）回复于 2006-12-17 10:27:31 得分 0
学习一下高手的经验Top
56 楼dawugui（潇洒老乌龟）回复于 2006-12-17 10:48:02 得分 0
xiaoku(野蛮人(^v^)) 的简单易懂.给分80.并已经使用到中央音乐学院的考试中.
coolingpipe(冷箫轻笛) 比较复杂.给分20.
其他人的不对.不给分.
再留一天,看看还有没有解法.(分数另给)Top
57 楼boster（）回复于 2006-12-17 17:57:47 得分 0
好像这样可以，先得出排名，再去掉前后几名，再group
select t2.id,avg(t2.score) from
(select id, score, teacher, rank() over(partiton by id order by score) rank,
count() over(partiton by id order by score) count from t1
) t2
where t2.count>=9
and t2.rank>=3 and t2.rank<=t2.count-2
group by t2.id
union all
select t3.id,avg(t3.score) from
(select id, score, teacher, rank() over(partiton by id order by score) rank,
count() over(partiton by id order by score) count from t1
) t3
where t3.count<=8
and t3.rank>=2 and t3.rank<=t3.count-1
group by t3.id
Top
58 楼playwarcraft（时间就像乳沟,挤挤还是有的）回复于 2006-12-17 21:06:07 得分 0
dawugui(潇洒老乌龟) ( ) 信誉：100 Blog 2006-12-17 10:48:02 得分: 0
xiaoku(野蛮人(^v^)) 的简单易懂.给分80.并已经使用到中央音乐学院的考试中.
coolingpipe(冷箫轻笛) 比较复杂.给分20.
其他人的不对.不给分.
再留一天,看看还有没有解法.(分数另给)
----------------------------------------------------------
乌龟太小气了,才100,就被分光了:(
偶快冷死啦!!!!!!!!!Top
59 楼playwarcraft（时间就像乳沟,挤挤还是有的）回复于 2006-12-17 21:21:19 得分 0
select id , avg(1.0*score) as score
from
( select id,score from T a
where teacher not in (select top 2 teacher from T where id=a.id order by score desc)
and teacher not in (select top 2 teacher from T where id=a.id order by score)
and (select count(*) from T where id=a.id)>=9
union all
select id,score from T b
where teacher not in (select top 1 teacher from T where id=b.id order by score desc)
and teacher not in (select top 1 teacher from T where id=b.id order by score)
and (select count(*) from T where id=b.id)<=8
) TT
group by idTop
60 楼ss3idx（）回复于 2006-12-17 23:30:40 得分 0
顶啊...牛人真多...Top
61 楼bj_cash2006（寒裂天）回复于 2006-12-18 09:20:36 得分 0
学习Top
62 楼wizardspell（）回复于 2006-12-18 10:21:41 得分 0
mark
n多牛Top
63 楼hunhun02（永不放弃）回复于 2006-12-18 15:33:54 得分 0
学习Top
64 楼kourr2004（★★★★★鬼谷子：）★★★★★）回复于 2006-12-18 15:49:50 得分 0
牛人真多，学习了：）从dawugui (潇洒老乌龟)这学到了不少东西，向你致敬Top
65 楼stove（火狐）回复于 2006-12-18 15:58:02 得分 0
我来学习学习Top
66 楼mxmartin（一生叹息）回复于 2006-12-18 18:26:54 得分 0
学习学习,水平还不到!Top
67 楼panqifeng440（盘奇峰）回复于 2006-12-18 20:01:22 得分 0
冷萧轻笛 你也太厉害了，弄得我头晕。
像你这种代码起码应该写写大概的思路吧。Top
68 楼CaiNiaoWuZui（）回复于 2006-12-18 20:28:12 得分 0
学习Top
69 楼renfeiyang（任飞扬）回复于 2006-12-18 20:47:29 得分 0
学习Top
