DENSE_RANK

声明

DENSE_RANK() OVER([ query_partition_clause ] order_by_clause)

说明

计算每一行数据在某列上的排序，该列由 order_by_clause 中的列决定。

排名是连续的整数。对于相同排名的行，排名数值相同。

示例

CREATE TABLE employees(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
INSERT INTO employees VALUES('JIM', 2000, 'CLEANER');
INSERT INTO employees VALUES('MIKE', 12000, 'ENGINEERING');
INSERT INTO employees VALUES('LILY', 13000, 'ENGINEERING');
INSERT INTO employees VALUES('IRIS', 11000, 'ENGINEERING');
INSERT INTO employees VALUES('TOM', 11000, 'ENGINEERING');
COMMIT;
Query OK, 0 rows affected (0.000 sec)

SELECT LAST_NAME, RANK() OVER(PARTITION BY JOB_ID ORDER BY SALARY) RANK, DENSE_RANK() OVER(PARTITION BY JOB_ID ORDER BY SALARY) DENSE_RANK, PERCENT_RANK() OVER(PARTITION BY JOB_ID ORDER BY SALARY) PERCENT_RANK FROM employees;
+-----------+------+------------+----------------------------------+
| LAST_NAME | RANK | DENSE_RANK | PERCENT_RANK                     |
+-----------+------+------------+----------------------------------+
| JIM       |    1 |          1 | 0.000000000000000000000000000000 |
| TOM       |    1 |          1 | 0.000000000000000000000000000000 |
| IRIS      |    1 |          1 | 0.000000000000000000000000000000 |
| MIKE      |    3 |          2 | 0.666666666666666666666666666667 |
| LILY      |    4 |          3 | 1.000000000000000000000000000000 |
+-----------+------+------------+----------------------------------+
5 rows in set (0.002 sec)