MySQL SET 在第一列和第二列上的排名

Question

我正在尝试按第一列和第二列进行查询设置排名列。就像 MySQL 不存在的 Rank over Partition

例如，

来自

+----+-------+--------+------+
| id | First | Second | Rank |
+----+-------+--------+------+
|  1 | a     |     10 |      |
|  2 | a     |      9 |      |
|  3 | b     |     10 |      |
|  4 | b     |      7 |      |
|  5 | a     |      1 |      |
|  6 | b     |      1 |      |
+----+-------+--------+------+

至

+----+-------+--------+------+
| id | First | Second | Rank |
+----+-------+--------+------+
|  1 | a     |     10 |    3 |
|  2 | a     |      9 |    2 |
|  3 | b     |     10 |    3 |
|  4 | b     |      7 |    2 |
|  5 | a     |      1 |    1 |
|  6 | b     |      1 |    1 |
+----+-------+--------+------+

排名不再继续。当到达“First”列的“a”的最后一个值时，它会再次从 1 开始。

并且它必须是 SET 而不是 SELECT。我不介意使用 SELECT 但我的观点是我不是试图从数据库检索数据而是设置值。

提前干杯，伙伴们。

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,first CHAR(1) NOT NULL
,second INT NOT NULL
);

INSERT INTO my_table VALUES
(1,'a',10),
(2,'a',9),
(3,'b',10),
(4,'b',7),
(5,'a',1),
(6,'b',1);

SELECT id
     , first
     , second
     , rank 
  FROM 
     ( SELECT x.* 
            , CASE WHEN @prev = first THEN @i:=@i+1 ELSE @i:=1 END rank
            , @prev:=first 
         FROM my_table x
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY first
            , second
            , id
     ) a
 ORDER 
    BY id;
+----+-------+--------+------+
| id | first | second | rank |
+----+-------+--------+------+
|  1 | a     |     10 |    3 |
|  2 | a     |      9 |    2 |
|  3 | b     |     10 |    3 |
|  4 | b     |      7 |    2 |
|  5 | a     |      1 |    1 |
|  6 | b     |      1 |    1 |
+----+-------+--------+------+
6 rows in set (0.00 sec)