SQL分组查询前n条最小记要_Sql Server

SQL分组查询前n条最小记录


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)
)
go

多个城市，多个酒店，每个酒店只有一个价格，如何查出每个城市的价格最低的前5家酒店，求协助
------解决方案--------------------
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
------解决方案--------------------

---方法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 desc) as b

最低的

---方法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