MySQL 5.6-类似于DENSE_RANK的功能，无需订购 [英] MySQL 5.6 - DENSE_RANK like functionality without 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等).但是，分配给特定groupVarian的nameVarian值应按照caseID的顺序(在表中出现的顺序).

I would like to get a new column nameVarian, such that same groupVarian values have same ranking represented by nameVarian (eg: v1, v2 and so on). However, nameVarian values assigned to a specific groupVarian should be as per the order of caseID (in the order they appear inside the table).

输出应类似于:

+------+-----------+----------+
|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版本< 8.0 ( OP的版本是5.6 ):

问题陈述似乎需要 DENSE_RANK 功能超过groupVarian;但是事实并非如此. 如 @Gordon Linoff 所述:

The problem statement looks like needing DENSE_RANK functionality over groupVarian; however it is not. As explained by @Gordon Linoff:

您似乎希望按它们在广告中出现的顺序进行枚举 数据.

You appear to want them enumerated by the order they appear in the data.

假设您的表名是t(请相应地为您的代码更改表名和字段名).这是一种利用会话变量的方法(适用于旧版本的MySQL )，得到预期的结果( DB提琴 ):

Assuming that your table name is t (please change the table and field name(s) accordingly for your code). Here is an approach utilizing session variables (for older versions of MySQL), giving the desired result (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功能.但是，以下查询会更高效，因为它会创建较小的派生表，并避免在groupVarian上使用 JOIN :

Additionally: My earlier attempt to emulate DENSE_RANK functionality, works well. Although previous query can also be tweaked slightly to achieve DENSE_RANK functionality. However, the following query is more efficient, as it creates lesser Derived tables, and avoids 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 

这篇关于MySQL 5.6-类似于DENSE_RANK的功能，无需订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！