P粉7574324912023-08-24 20:59:09
這是一個通用解決方案,它將分割區上的密集等級分配給行。它使用使用者變數:
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 |
P粉2183619722023-08-24 09:36:05
一種選擇是使用排名變量,如下所示:
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)