例子:找出每个歌手销量最高的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