对于 (OP的版本是5.6):
问题陈述看起来需要DENSE_RANK功能groupVarian
; 但是事实并非如此。正如 @Gordon Linoff 解释的那样 :
您似乎希望按它们在数据中出现的顺序来枚举它们。
假设您的表名是t
(请为您的代码相应地更改表名和字段名)。这是一种利用会话变量的方法( ),给出期望的结果( ):
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
功能。但是,以下查询效率更高,因为它创建的 ,并避免了 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