SQL rank, row_number, dense_rank 区别
You will only see the difference if you have ties within a partition for a particular ordering value.
RANK
and DENSE_RANK
are deterministic in this case, all rows with the same value for both the ordering and partitioning columns will end up with an equal result, whereas ROW_NUMBER
will arbitrarily (non deterministically) assign an incrementing result to the tied rows.
Example: (All rows have the same StyleID
so are in the same partition and within that partition the first 3 rows are tied when ordered by ID
)
示例
```sql
WITH T(StyleID, ID)
AS (SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,1 UNION ALL
SELECT 1,2)
SELECT *,
RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS 'RANK',
ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) AS 'ROW_NUMBER',
DENSE_RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS 'DENSE_RANK'
FROM T
运行结果
StyleID ID RANK ROW_NUMBER DENSE_RANK
----------- -------- --------- --------------- ----------
1 1 1 1 1
1 1 1 2 1
1 1 1 3 1
1 2 4 4 2
#搬运来源https://stackoverflow.com/questions/7747327/sql-rank-versus-row-number