MySQL 5.6-类似于DENSE_RANK的功能，无需订购

Question

我有一张这样的桌子：

+------+-----------+
|caseID|groupVarian|
+------+-----------+
|1     |A,B,C,D,E  |
+------+-----------+
|2     |A,B,N,O,P  |
+------+-----------+
|3     |A,B,N,O,P  |
+------+-----------+
|4     |A,B,C,D,F  |
+------+-----------+
|5     |A,B,C,D,E  |
+------+-----------+

我想获得一个新列nameVarian，以使相同的groupVarian值具有由表示的相同排名nameVarian（例如：v1，v2等）。但是，nameVarian分配给特定值的值groupVarian应按照caseID（在表中出现的顺序）的顺序。

输出应该是这样的：

+------+-----------+----------+
|caseID|groupVarian|namevarian
+------+-----------+----------+
|1     |A,B,C,D,E  |v1        |
+------+-----------+----------+
|2     |A,B,N,O,P  |v2        |
+------+-----------+----------+
|3     |A,B,N,O,P  |v2        |
+------+-----------+----------+
|4     |A,B,C,D,F  |v3        |
+------+-----------+----------+
|5     |A,B,C,D,E  |v1        |
+------+-----------+----------+

Answer 1

对于 MySQL版本
<8.0
（OP的版本是5.6）：

问题陈述看起来需要DENSE_RANK功能groupVarian; 但是事实并非如此。正如 @Gordon
Linoff

解释的

那样 ：

您似乎希望按它们在数据中出现的顺序来枚举它们。

假设您的表名是t（请为您的代码相应地更改表名和字段名）。这是一种利用会话变量的方法（
对于MySQL的较早版本 ），给出期望的结果（ DB Fiddle ）：

SET @row_number = 0;
SELECT t3.caseID, 
       t3.groupVarian, 
       CONCAT('v', t2.num) AS nameVarian
FROM
  (
   SELECT 
     (@row_number:=@row_number + 1) AS num, 
     t1.groupVarian 
   FROM 
     (
      SELECT DISTINCT groupVarian 
      FROM t 
      ORDER BY caseID ASC 
     ) AS t1 
  ) AS t2 
INNER JOIN t AS t3 
  ON t3.groupVarian = t2.groupVarian 
ORDER BY t3.caseID ASC

另外：
我之前的模拟DENSE_RANK功能的尝试效果很好。尽管也可以对先前的查询进行一些微调以实现DENSE_RANK功能。但是，以下查询效率更高，因为它创建的
Derived表较少 ，并避免了 JOIN on groupVarian：

SET @row_number = 1;
SET @group_varian = '';

SELECT inner_nest.caseID, 
       inner_nest.groupVarian, 
       CONCAT('v', inner_nest.num) as nameVarian 
FROM (
        SELECT 
            caseID, 
            @row_number:=CASE
                           WHEN @group_varian = groupVarian THEN @row_number
                           ELSE @row_number + 1
                         END AS num, 
            @group_varian:=groupVarian as groupVarian 
        FROM
            t  
        ORDER BY groupVarian
     ) AS inner_nest 
ORDER BY inner_nest.caseID ASC