mysql实现dense_rank_MySQL 5.6-类似于DENSE_RANK的功能，无需订购

I have a table like this:

+------+-----------+

|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 |

+------+-----------+

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).

The output should be something like:

+------+-----------+----------+

|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 |

+------+-----------+----------+

解决方案

For MySQL version < 8.0 (OP's version is 5.6):

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.

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 (

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

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