Il s'agit d'une solution générale qui attribue des niveaux denses sur les partitions aux lignes. Il utilise des variables utilisateur :

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;

Veuillez noter que l'affectation des variables se trouve enCASE表达式内。这（理论上）解决了评估顺序问题。添加IS NOT NULLpour gérer les problèmes de conversion de type de données et de court-circuit.

PS : cela peut être facilement converti en numéros de ligne sur la partition en supprimant toutes les conditions qui vérifient les liens.

| 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 |

démo db fiddle

Une option consiste à utiliser une variable de classement comme celle-ci :

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

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

Cas de test :

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');

Résultat :

+------------+------+--------+------+ | 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)