这是一个通用解决方案，它将分区上的密集等级分配给行。它使用用户变量：

CREATE TABLE person ( id INT NOT NULL PRIMARY KEY, firstname VARCHAR(10), gender VARCHAR(1), age INT ); INSERT INTO person (id, firstname, gender, age) VALUES (1, 'Adams', 'M', 33), (2, 'Matt', 'M', 31), (3, 'Grace', 'F', 25), (4, 'Harry', 'M', 20), (5, 'Scott', 'M', 30), (6, 'Sarah', 'F', 30), (7, 'Tony', 'M', 30), (8, 'Lucy', 'F', 27), (9, 'Zoe', 'F', 30), (10, 'Megan', 'F', 26), (11, 'Emily', 'F', 20), (12, 'Peter', 'M', 20), (13, 'John', 'M', 21), (14, 'Kate', 'F', 35), (15, 'James', 'M', 32), (16, 'Cole', 'M', 25), (17, 'Dennis', 'M', 27), (18, 'Smith', 'M', 35), (19, 'Zack', 'M', 35), (20, 'Jill', 'F', 25); SELECT person.*, @rank := CASE WHEN @partval = gender AND @rankval = age THEN @rank WHEN @partval = gender AND (@rankval := age) IS NOT NULL THEN @rank + 1 WHEN (@partval := gender) IS NOT NULL AND (@rankval := age) IS NOT NULL THEN 1 END AS rnk FROM person, (SELECT @rank := NULL, @partval := NULL, @rankval := NULL) AS x ORDER BY gender, age;

请注意，变量赋值位于CASE表达式内。这（理论上）解决了评估顺序问题。添加IS NOT NULL是为了处理数据类型转换和短路问题。

PS：通过删除所有检查平局的条件，可以轻松地将其转换为分区上的行号。

| id | firstname | gender | age | rank | |----|-----------|--------|-----|------| | 11 | Emily | F | 20 | 1 | | 20 | Jill | F | 25 | 2 | | 3 | Grace | F | 25 | 2 | | 10 | Megan | F | 26 | 3 | | 8 | Lucy | F | 27 | 4 | | 6 | Sarah | F | 30 | 5 | | 9 | Zoe | F | 30 | 5 | | 14 | Kate | F | 35 | 6 | | 4 | Harry | M | 20 | 1 | | 12 | Peter | M | 20 | 1 | | 13 | John | M | 21 | 2 | | 16 | Cole | M | 25 | 3 | | 17 | Dennis | M | 27 | 4 | | 7 | Tony | M | 30 | 5 | | 5 | Scott | M | 30 | 5 | | 2 | Matt | M | 31 | 6 | | 15 | James | M | 32 | 7 | | 1 | Adams | M | 33 | 8 | | 18 | Smith | M | 35 | 9 | | 19 | Zack | M | 35 | 9 |

db 演示fiddle

一种选择是使用排名变量，如下所示：

SELECT first_name, age, gender, @curRank := @curRank + 1 AS rank FROM person p, (SELECT @curRank := 0) r ORDER BY age;

(SELECT @curRank := 0)部分允许变量初始化，而无需单独的SET命令。

测试用例：

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1)); INSERT INTO person VALUES (1, 'Bob', 25, 'M'); INSERT INTO person VALUES (2, 'Jane', 20, 'F'); INSERT INTO person VALUES (3, 'Jack', 30, 'M'); INSERT INTO person VALUES (4, 'Bill', 32, 'M'); INSERT INTO person VALUES (5, 'Nick', 22, 'M'); INSERT INTO person VALUES (6, 'Kathy', 18, 'F'); INSERT INTO person VALUES (7, 'Steve', 36, 'M'); INSERT INTO person VALUES (8, 'Anne', 25, 'F');

结果：

+------------+------+--------+------+ | first_name | age | gender | rank | +------------+------+--------+------+ | Kathy | 18 | F | 1 | | Jane | 20 | F | 2 | | Nick | 22 | M | 3 | | Bob | 25 | M | 4 | | Anne | 25 | F | 5 | | Jack | 30 | M | 6 | | Bill | 32 | M | 7 | | Steve | 36 | M | 8 | +------------+------+--------+------+ 8 rows in set (0.02 sec)