34,591
社区成员
发帖
与我相关
我的任务
分享
set nocount on
create table test(S# varchar(20),SNAME varchar(20),GRADE varchar(20),TIME varchar(20))
insert into test select '1','张三','90','2008-05-04'
insert into test select '1','张三','70','2007-09-01'
insert into test select '1','张三','50','2006-02-01'
insert into test select '1','张三','56','2004-07-15'
insert into test select '1','张三','70','2008-04-06'
insert into test select '2','李四','60','2005-02-13'
insert into test select '2','李四','30','2008-04-10'
insert into test select '2','李四','80','2008-01-18'
go
--测试
select * from (select *,
row_number() over(partition by S# order by GRADE desc) as rowindex
from test) as a
where rowindex<=(select count(*) from test where S#=a.S#)*0.5
--删除测试环境
drop table test
set nocount off
/*
1 张三 90 2008-05-04 1
1 张三 70 2007-09-01 2
2 李四 80 2008-01-18 1*/
select * from Test as t where TIME in (select top 10 percent TIME from Test where S#=t.S# order by GRADE desc, TIME desc)