ROW_NUMBER、RANK、DENSE_RANK函數和OVER的使用
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
ROW_NUMBER()
/*以FoodPrice列排序并顯示排序後的行号*/
SELECT ROW_NUMBER() OVER(ORDER BY FoodPrice DESC) AS RowId,* FROM dbo.Food
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnLwgzNxETM1gTM3IzMwkTMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
/*以FoodType分組,然後以FoodPrice排序并顯示所在分組的相應行号*/
SELECT ROW_NUMBER() OVER(PARTITION BY FoodType ORDER BY FoodPrice DESC) AS RowId,* FROM dbo.Food
RANK()
/*以FoodType分組,然後以FoodPrice排序并顯示所在分組的相應行号,如果排序字段字相等則顯示相同的行号,
并跳過所在的真實行号*/
SELECT RANK() OVER(PARTITION BY FoodType ORDER BY FoodPrice) AS RowID,* FROM dbo.Food
DENSE_RANK()
/*以FoodType分組,然後以FoodPrice排序并顯示所在分組的相應行号,如果排序字段字相等則顯示相同的行号,
忽略相同行号,依次遞增*/
SELECT DENSE_RANK() OVER(PARTITION BY FoodType ORDER BY FoodPrice) AS RowID,* FROM dbo.Food
文章轉載自:https://www.cnblogs.com/ianunspace/p/5057333.html