sql - 为每个组选择前 (N) 行

Question

到目前为止，这是我的查询:

SELECT [Id], [HotelName], [StarRating], [Description], [CheckinDate], [CheckoutDate], [Price], [ImageUrl]
FROM
(
    SELECT TOP (6) [Id], [HotelName], [StarRating], [Description], [CheckinDate], [CheckoutDate], [Price], [ImageUrl], RANK() OVER(PARTITION BY [StarRating] ORDER BY [StarRating]) AS Num
    FROM [dbo].[Hotel]
    WHERE [CityId] = @CityId 
    AND CheckinDate > GETDATE()
    AND [StarRating] IN (3, 4, 5)
) X
WHERE Num <= 2

我想要的是为每个星级获得 2 行:3 级中的 2 行，4 级中的 2 行和 5 级中的 2 行。我该怎么做？我已经在网上做了一些研究之后想出了以上内容，但我显然不完全理解如何实现它，因为它不起作用......我得到 6 行星级 3

Answer 1

使用 ROW_NUMBER 函数 - 例如，

WITH X 
AS
(
    SELECT 
       [Id], [HotelName], [StarRating], [Description], 
       [CheckinDate], [CheckoutDate], [Price], [ImageUrl], 
       ROW_NUMBER() OVER(PARTITION BY [StarRating] ORDER BY [Id]) AS Num
    FROM 
       [dbo].[Hotel]
    WHERE 
       [CityId] = @CityId 
         AND CheckinDate > GETDATE()
         AND [StarRating] IN (3, 4, 5)
) 
SELECT 
   [Id], [HotelName], [StarRating], [Description], 
   [CheckinDate], [CheckoutDate], [Price], [ImageUrl]
FROM 
   X
WHERE 
   Num <= 2