2005及以上with cte as( select *,row_number over(partition by cityid,hotelid order by amount ) ino ) select id,hotelid,cityid,amount from cte where ino<=5
with cte as( select *,row_number() over(partition by cityid,hotelid order by amount ) ino ) select id,hotelid,cityid,amount from cte where ino<=5
---方法2: select b.* from HotelPrices as a cross apply (select top 5 * from HotelPrices where CityID=a.CityID order by amount desc) as b
if object_id('HotelPrices') is null create table HotelPrices ( ID int identity, HotelID int not null, CityID int not null, Amount decimal(18,4) null, constraint PK_HOTELPRICES primary key (ID) )insert HotelPrices select 1,1,10 union all select 2,1,101 union all select 3,1,101 union all select 4,1,102 union all select 5,1,13 union all select 6,1,8 union all select 7,2,105 union all select 8,2,101 union all select 9,2,101 union all select 10,2,102 union all select 11,2,130 union all select 12,2,80;with cte as( select *,row_number() over(partition by cityid order by amount ) ino from HotelPrices ) select id,hotelid,cityid,amount,ino from cte where ino<=5
最低的 ---方法2: select b.* from HotelPrices as a cross apply (select top 5 * from HotelPrices where CityID=a.CityID order by amount ) as b
分组排名可以考虑用row_number() over(partition by xxx order by xxx) 进行分组排名然后再取每组的前5
select *,row_number over(partition by cityid,hotelid order by amount ) ino
)
select id,hotelid,cityid,amount
from cte
where ino<=5
select *,row_number() over(partition by cityid,hotelid order by amount ) ino
)
select id,hotelid,cityid,amount
from cte
where ino<=5
select
b.*
from
HotelPrices as a
cross apply
(select top 5 * from HotelPrices where CityID=a.CityID order by amount desc) as b
create table HotelPrices (
ID int identity,
HotelID int not null,
CityID int not null,
Amount decimal(18,4) null,
constraint PK_HOTELPRICES primary key (ID)
)insert HotelPrices
select 1,1,10
union all
select 2,1,101
union all
select 3,1,101
union all
select 4,1,102
union all
select 5,1,13
union all
select 6,1,8
union all
select 7,2,105
union all
select 8,2,101
union all
select 9,2,101
union all
select 10,2,102
union all
select 11,2,130
union all
select 12,2,80;with cte as(
select *,row_number() over(partition by cityid order by amount ) ino from HotelPrices
)
select id,hotelid,cityid,amount,ino
from cte
where ino<=5
---方法2:
select
b.*
from
HotelPrices as a
cross apply
(select top 5 * from HotelPrices where CityID=a.CityID order by amount ) as b