SQL ROW_NUMBER 与 INNER JOIN

Question

我需要在以下查询中使用 ROW_NUMBER() 来返回结果的第 5 到 10 行。谁能告诉我我需要做什么？我一直在努力无济于事。如果有人可以提供帮助，我将不胜感激。

SELECT * 
FROM   villa_data 
       INNER JOIN villa_prices 
         ON villa_prices.starRating = villa_data.starRating 
WHERE  villa_data.capacity >= 3 
       AND villa_data.bedrooms >= 1 
       AND villa_prices.period = 'lowSeason' 
ORDER  BY villa_prices.price, 
          villa_data.bedrooms, 
          villa_data.capacity 

Answer 1

您需要将其粘贴在表表达式中以过滤 ROW_NUMBER .您将无法使用 *因为它会提示列名 starRating出现不止一次，因此需要明确列出所需的列。无论如何，这是更好的做法。

WITH CTE AS
(
SELECT /*TODO: List column names*/
       ROW_NUMBER() 
          OVER (ORDER BY villa_prices.price, 
                         villa_data.bedrooms, 
                         villa_data.capacity) AS RN
FROM   villa_data 
       INNER JOIN villa_prices 
         ON villa_prices.starRating = villa_data.starRating 
WHERE  villa_data.capacity >= 3 
       AND villa_data.bedrooms >= 1 
       AND villa_prices.period = 'lowSeason' 

)
SELECT /*TODO: List column names*/
FROM CTE
WHERE RN BETWEEN 5 AND 10
ORDER BY RN