MySQL 5.6 - DENSE_RANK 类似功能，没有 Order By

我有一张这样的表:+------+-----------+|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 |+------+-----------+----------+ 最佳答案 对于 MySQL 版本 ( OP's version is 5.6 ):问题陈述看起来需要 DENSE_RANK 功能而不是 groupVarian ；然而事实并非如此。 As explained by @Gordon Linoff : You appear to want them enumerated by the order they appear in the data.假设您的表名是 t(请根据您的代码相应地更改表名和字段名)。这是一个 approach utilizing session variables ( 用于旧版本的 MySQL )，给出所需的结果 ( DB Fiddle ):SET @row_number = 0;SELECT t3.caseID, t3.groupVarian, CONCAT('v', t2.num) AS nameVarianFROM ( SELECT (@row_number:=@row_number + 1) AS num, t1.groupVarian FROM ( SELECT DISTINCT groupVarian FROM t ORDER BY caseID ASC ) AS t1 ) AS t2INNER JOIN t AS t3 ON t3.groupVarian = t2.groupVarianORDER BY t3.caseID ASC 另外: 我之前尝试模拟 DENSE_RANK 功能，效果很好。虽然之前的查询也可以稍微调整以实现 DENSE_RANK 功能。但是，以下查询效率更高，因为它创建 较小的派生表 ，并避免 JOIN 对 groupVarian :SET @row_number = 1;SET @group_varian = '';SELECT inner_nest.caseID, inner_nest.groupVarian, CONCAT('v', inner_nest.num) as nameVarianFROM ( 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_nestORDER BY inner_nest.caseID ASC