查每个分類前N条记录

--> Title : 查每个地區前N条记录 假如3
--> Author : wufeng4552
--> Date : 2009-11-02
if object_id('test')is not null drop table test
go
create table test
(
ID int identity,
areaid int,
score int
)
insert into test select 0,10
union all select 0,20
union all select 0,30
union all select 0,40
union all select 0,50
union all select 1,10
union all select 1,20
union all select 1,30
union all select 1,40
union all select 1,50
union all select 2,10
union all select 2,20
union all select 2,30
union all select 2,40
union all select 2,50
go
--方法1 利用自增ID 适用于sql2000和2005
select areaid,
score
from test t
where id in(select top 3 id from test where areaid=t.areaid order by score desc)
--方法2 适用于sql2000和2005
select areaid,
score
from test t
where (select count(*) from test where areaid=t.areaid and score>t.score )<3
--方法3 checksum(*)适用于sql2000和2005
select areaid,
score
from test t
where checksum(*) in (select top 3 checksum(*) from test where areaid=t.areaid order by score desc)
--方法4 ROW_NUMBER()适用于2005
;with test1 as
(
select *,
row_number() over (partition by areaid order by score desc) as 'cnt'
from test
)
select areaid,
score
from test1
where cnt between 1 and 3;
--方法5 sql2005的cross apply
select distinct
t.areaid,
t.score
from test a
cross apply
(select top 3 areaid,score from test
where a.areaid=areaid order by score desc) as T
/*
areaid score
----------- -----------
0 30
0 40
0 50
1 30
1 40
1 50
2 30
2 40
2 50
*/