小編典典
對于 MySQL版本
<8.0(OP的版本是5.6)：
問題陳述看起來需要DENSE_RANK功能groupVarian; 但是事實并非如此。正如 @Gordon
Linoff解釋的那樣 ：
您似乎希望按它們在數據中出現的順序來枚舉它們。
假設您的表名是t(請為您的代碼相應地更改表名和字段名)。這是一種利用會話變量的方法(
對于MySQL的較早版本 )，給出期望的結果(
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功能。但是，以下查詢效率更高，因為它創建的
Derived表較少 ，并避免了 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
2020-05-17
