MySQL 5.6 - DENSE_RANK喜欢没有 Order By 的功能

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 等(。但是，分配给特定groupVariannameVarian值应按照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功能。但是，以下查询更有效，因为它创建的派生表较少，并避免groupVarianJOIN：

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 

Answer 2

你可以使用DENSE_RANK(MySQL 8.0(：

SELECT *, CONCAT('v', DENSE_RANK() OVER(ORDER BY groupVarian)) AS namevarian
FROM tab
ORDER BY CaseID;

数据库<>小提琴演示

Answer 3

基本上，你想要枚举变体。 如果你只想要一个数字，那么你可以使用最小id：

select t.*, min_codeId as groupVariantId
from t join
(select groupVariant, min(codeId) as min_codeId
from t
group by groupVariant
) g
on t.groupVariant = g.groupVariant;

但这并不完全是你想要的。 您似乎希望按它们在数据中的显示顺序枚举它们。 为此，您需要变量。 这有点棘手，但是：

select t.*, rn as groupVariantId
from t join
(select g.*,
(@rn := if(@gv = groupvariant, @gv,
if(@gv := groupvariant, @gv+1, @gv+1)
)
) as rn
from (select groupVariant, min(codeId) as min_codeId
from t
group by groupVariant
order by min(codeId)
) g cross join
(select @gv := '', @rn := 0) params
) g
on t.groupVariant = g.groupVariant;

使用变量很棘手。 一个重要的考虑因素：MySQL不保证SELECT中表达式的计算顺序。 这意味着变量不应该在一个表达式中赋值，然后在另一个表达式中使用——因为它们可能以错误的顺序计算(另一个答案有这个错误(。

此外，order by需要在子查询中进行。 MySQL不保证变量赋值在排序之前发生。