遇到的问题是这样的:
学生评教系统
student_id teacher_id course_id
--------------------------------------------------
001 001 001 100
002 001 001 80
003 001 001 70
004 001 001 100
005 001 001 80
006 001 001 70
007 001 001 100
008 001 001 80
009 001 001 70
010 001 001 70需要统计老师001的得分,需要去掉10%的最高分,10%的最低分,呵呵,这样的sql语句该怎么实现,是否需要通过自定义函数来实现,如果需要自定义函数,则这个函数该如何去实现?
谢谢各位了!!!!!
学生评教系统
student_id teacher_id course_id
--------------------------------------------------
001 001 001 100
002 001 001 80
003 001 001 70
004 001 001 100
005 001 001 80
006 001 001 70
007 001 001 100
008 001 001 80
009 001 001 70
010 001 001 70需要统计老师001的得分,需要去掉10%的最高分,10%的最低分,呵呵,这样的sql语句该怎么实现,是否需要通过自定义函数来实现,如果需要自定义函数,则这个函数该如何去实现?
谢谢各位了!!!!!
那就是11-90%select avg() as from
(
select top percent 90 * from tb where student_id no in (select top percent 10 student_id from tb order by ) order by
) t
(
select top percent 90 * from tb where teacher_id = '001' and student_id no in (select top percent 10 student_id from tb where teacher_id = '001' order by ) order by
) t
teacher
---------------
001 80
002 50
呵呵,但是您的思路很好,我看看能不能够解决分组的问题
下面这个比较合适.
如何取每个种类的前百分之x的记录示例数据(表a):
area cust money
--------------
A 123 20.0
A 159 20.0
A 456 25.0
A 483 30.0
A 789 40.0
A 597 50.0
B 147 10.0
B 258 20.0
B 369 25.0
B 384 30.0
希望得到的如下结果
area cust money
--------------
A 483 30.0
A 789 40.0
A 597 50.0
B 369 25.0
B 384 30.0
现有表a,想得到表a中各地区(area)的商户(cust)交易金额(money)排该地区里面前百分之50%的记录.
即要:
地区A中金额前百分之50%
地区B中金额前百分之50%
....C..............50%
....D..............50%
...................... CREATE TABLE #a (
[area] [char] (10),
[cust] [char] (10),
[money] [numeric](10, 1) NULL
)insert into #a(area,cust,money) values('A','123',20.0)
insert into #a(area,cust,money) values('A','159',20.0)
insert into #a(area,cust,money) values('A','456',25.0)
insert into #a(area,cust,money) values('A','483',30.0)
insert into #a(area,cust,money) values('A','789',40.0)
insert into #a(area,cust,money) values('A','597',50.0)
insert into #a(area,cust,money) values('B','147',10.0)
insert into #a(area,cust,money) values('B','258',20.0)
insert into #a(area,cust,money) values('B','369',25.0)
insert into #a(area,cust,money) values('B','384',30.0) select * from #a t
where cust in
(
select top 50 percent cust from #a where area=t.area order by money desc
)drop table #a//结果
area cust money
---------- ---------- ------------
A 483 30.0
A 789 40.0
A 597 50.0
B 369 25.0
B 384 30.0(所影响的行数为 5 行)
换成取11-90%的语句,应该行.
select teacher_id,avg(percent) as from
(
select top 90 percent * from tempMark where student_id not in (select top 10 percent student_id from tempMark order by percent) order by percent
) t group by teacher_id这个是我当前的答案,后面还需要处理动态的区间(前后N%),呵呵,程序可以生成这样的东西了;