Maison > Questions et réponses > le corps du texte
P粉7574324912023-08-24 20:59:09
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 en CASE
表达式内。这(理论上)解决了评估顺序问题。添加 IS NOT NULL
pour 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 |
P粉2183619722023-08-24 09:36:05
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)
部分允许变量初始化,而无需单独的 SET
Commandement.
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)