我想按typeid分组获取每个类别中前10个的数据怎么获取。也就是每个typeID中前10个人员的数据集?
如下表数据所示:name age typeID score
王某 22 2 40
孙某 23 1 60
黄某 18 2 70
张某 17 1 60
杜某 16 4 70
黄某 15 3 70
高某 14 1 60
宋某 13 2 70
如下表数据所示:name age typeID score
王某 22 2 40
孙某 23 1 60
黄某 18 2 70
张某 17 1 60
杜某 16 4 70
黄某 15 3 70
高某 14 1 60
宋某 13 2 70
from tb t
where (select count(1) from tb where typeid=t.typeid and score>=t.score)<=10
select *
from(
select *,ROW_NUMBER() over(partition by typeID order by score desc) row
from [table]) t
where row<=10
;with a as
( select *,idd=row_number() over(partition by typeid order by score desc ) from tb
)
select a.name,a.age,a.typeID,a.score from a where a.idd <11
select *
from(
select *,rank() over(partition by typeID order by score desc) row
from [table]) t
where row<=10