SQL Server2005 apply的一些运用

例子：找出每个歌手销量最高的3首歌
create table singer_info
(
id int primary key identity(1,1),
name varchar(20),
song varchar(20),
amount int
)
 
insert into singer_info values('jay','aaa',1000)
insert into singer_info values('jay','bbb',2000)
insert into singer_info values('jay','ccc',3000)
insert into singer_info values('jay','ddd',4000)
insert into singer_info values('jay','eee',5000)
insert into singer_info values('jay1','fff',1000)
insert into singer_info values('jay1','ggg',2000)
insert into singer_info values('jay1','hhh',3000)
insert into singer_info values('jay1','iii',4000)
insert into singer_info values('jay2','jjj',1000)
insert into singer_info values('jay2','kkk',2000)
insert into singer_info values('jay2','lll',3000)
insert into singer_info values('jay2','mmm',4000)
insert into singer_info values('jay3','nnn',1000)
 
(1)使用function apply
create function gettop
(@name varchar(20))
returns table
as
return (select top(3)id,name,song,amount
from singer_info
where name = @name
order by amount desc)
 
select distinct b.id,b.name,b.song,b.amount
from singer_info a
cross apply
gettop(a.name)as b
 
(2)使用apply
select distinct c.id,c.name,c.song,c.amount
from singer_info a
cross apply
(select top(3)id,name,song,amount
from singer_info b
where b.name = a.name
order by amount desc) as c
order by c.name asc,c.amount desc
 
(3)使用over partition by
select * from
(select a.id,a.name,a.song,a.amount,
row_number() over(partition by a.name order by a.name,a.amount desc) rn
from singer_info a)b
where b.rn<=3