/* Available in MySQL, SQL Server, PostgreSQL */
SELECT
Name,
CountryCode,
ROW_NUMBER() over p_code AS _row_number,
RANK() over p_code AS _rank,
DENSE_RANK() over p_code AS _dense_rank
FROM city
WHERE
Name = 'San Jose'
WINDOW p_code AS (PARTITION BY Name ORDER BY CountryCode)
| Name |
CountryCode |
_row_number |
_rank |
_dense_rank |
| San José |
CRI |
1 |
1 |
1 |
| San Jose |
PHL |
2 |
2 |
2 |
| San Jose |
PHL |
3 |
2 |
2 |
| San Jose |
USA |
4 |
4 |
3 |
| ROW_NUMBER() 计算行在分组内的序号，不管值是否重复。
RANK() 计算每一行在分组内的名次。遇到相同的值，这些行得到相同的名次，随后的名次会被跳过。
DENSE_RANK() 与 RANK() 相同，但值相同时不跳号，名次连续排下去。 |