刨除最高分最低分的算法

Question

http://community.csdn.net/Expert/topic/5139/5139700.xml?temp=.4085504
谢谢各位在这个帖子里面的回答，但是经过我的测试，发现有问题测试数据
student_id     teacher_id     course_id        percentMark
010        001        001        91.0
009        001        001        92.0
008        001        001        93.0
007        001        001        94.0
006        001        001        95.0
005        001        001        96.0
004        001        001        97.0
003        001        001        98.0
002        001        001        99.0
001        001        001        100.0
001        002        001        91.0
002        002        001        92.0
003        002        001        93.0
004        002        001        94.0
005        002        001        95.0
006        002        001        96.0
007        002        001        97.0
008        002        001        98.0
009        002        001        99.0
010        002        001        100.0希望的到的结果
010        001        001        91.0  （本行没有）
009        001        001        92.0
008        001        001        93.0
007        001        001        94.0
006        001        001        95.0
005        001        001        96.0
004        001        001        97.0
003        001        001        98.0
002        001        001        99.0
001        001        001        100.0 （本行没有）
001        002        001        91.0  （本行没有）
002        002        001        92.0
003        002        001        93.0
004        002        001        94.0
005        002        001        95.0
006        002        001        96.0
007        002        001        97.0
008        002        001        98.0
009        002        001        99.0
010        002        001        100.0  （本行没有）
看看该如何处理？就是去掉每个老师得分中的10%最高分和10%最低分，呵呵

Answer 1

declare @t table(student_id varchar(8),teacher_id varchar(8),course_id varchar(8),percentMark numeric(4,1))
insert into @t select '010','001','001',91.0
insert into @t select '009','001','001',92.0
insert into @t select '008','001','001',93.0
insert into @t select '007','001','001',94.0
insert into @t select '006','001','001',95.0
insert into @t select '005','001','001',96.0
insert into @t select '004','001','001',97.0
insert into @t select '003','001','001',98.0
insert into @t select '002','001','001',99.0
insert into @t select '001','001','001',100.0
insert into @t select '001','002','001',91.0
insert into @t select '002','002','001',92.0
insert into @t select '003','002','001',93.0
insert into @t select '004','002','001',94.0
insert into @t select '005','002','001',95.0
insert into @t select '006','002','001',96.0
insert into @t select '007','002','001',97.0
insert into @t select '008','002','001',98.0
insert into @t select '009','002','001',99.0
insert into @t select '010','002','001',100.0
select
    t.*
from
    @t t
where
    t.student_id not in(select top 10 percent student_id from @t where teacher_id=t.teacher_id and course_id=t.course_id order by percentMark desc)
    and
    t.student_id not in(select top 10 percent student_id from @t where teacher_id=t.teacher_id and course_id=t.course_id order by percentMark asc)/*
student_id teacher_id course_id percentMark 
---------- ---------- --------- ----------- 
009        001        001       92.0
008        001        001       93.0
007        001        001       94.0
006        001        001       95.0
005        001        001       96.0
004        001        001       97.0
003        001        001       98.0
002        001        001       99.0
002        002        001       92.0
003        002        001       93.0
004        002        001       94.0
005        002        001       95.0
006        002        001       96.0
007        002        001       97.0
008        002        001       98.0
009        002        001       99.0
*/

Answer 2

-- 如果楼主用sql 2005, 则可以试试 sql 2005 的排名函数来处理declare @t table(id int identity(1,1), student_id varchar(8),teacher_id varchar(8),course_id varchar(8),percentMark numeric(4,1))
insert into @t select '010','001','001',91.0
insert into @t select '009','001','001',92.0
insert into @t select '008','001','001',93.0
insert into @t select '007','001','001',94.0
insert into @t select '006','001','001',95.0
insert into @t select '005','001','001',96.0
insert into @t select '004','001','001',97.0
insert into @t select '003','001','001',98.0
insert into @t select '002','001','001',99.0
insert into @t select '001','001','001',100.0
insert into @t select '001','002','001',91.0
insert into @t select '002','002','001',92.0
insert into @t select '003','002','001',93.0
insert into @t select '004','002','001',94.0
insert into @t select '005','002','001',95.0
insert into @t select '006','002','001',96.0
insert into @t select '007','002','001',97.0
insert into @t select '008','002','001',98.0
insert into @t select '009','002','001',99.0
insert into @t select '010','002','001',100.0-- 删除
DELETE A
FROM @t A, (
SELECT gid = NTILE(10) OVER(PARTITION BY teacher_id ORDER BY percentMark), id
FROM @t
)B
WHERE A.id = b.id
AND B.gid IN(1, 10)-- 结果
SELECT * FROM @t

Answer 3

2000 里面, 考虑一下用临时表
即类似这样处理:declare @t table(id int identity(1,1), student_id varchar(8),teacher_id varchar(8),course_id varchar(8),percentMark numeric(4,1))
insert into @t select '010','001','001',91.0
insert into @t select '009','001','001',92.0
insert into @t select '008','001','001',93.0
insert into @t select '007','001','001',94.0
insert into @t select '006','001','001',95.0
insert into @t select '005','001','001',96.0
insert into @t select '004','001','001',97.0
insert into @t select '003','001','001',98.0
insert into @t select '002','001','001',99.0
insert into @t select '001','001','001',100.0
insert into @t select '001','002','001',91.0
insert into @t select '002','002','001',92.0
insert into @t select '003','002','001',93.0
insert into @t select '004','002','001',94.0
insert into @t select '005','002','001',95.0
insert into @t select '006','002','001',96.0
insert into @t select '007','002','001',97.0
insert into @t select '008','002','001',98.0
insert into @t select '009','002','001',99.0
insert into @t select '010','002','001',100.0-- 生成临时表
SELECT id = id * 1, gid = IDENTITY(int, 1, 1), teacher_id
INTO #
FROM @t 
ORDER BY teacher_id, percentMark-- 从临时表中选出最前和最后10%的记录, 然后与原表关联做删除
DELETE A
FROM @t A, (
SELECT A.id, gid = (A.gid - B.gid)  / B.g
FROM # A,(
SELECT 
teacher_id, 
gid = MIN(gid),
g = CONVERT(int, CEILING(COUNT(*) / 10.))
FROM #
GROUP BY teacher_id
)B
WHERE A.teacher_id = B.teacher_id
)B
WHERE A.id = B.id
AND B.gid IN(0, 9)-- 结果
SELECT * FROM @t
GODROP TABLE #

Answer 4

临时表应该会比直接做top 处理快一些