如何用SQL为重复name列值追加指定字符串并返回结果

现有超200万行数据，示例数据如下：

id,     name,     latitude,      longitude
S226,   Farm 1,   -87.511372,     176.831784
S011,   Farm 1,    -3.978185,     -173.035526
S540,   Farm 10,   20.242715,     -119.461222
S795,   Farm 10,   -49.680679,    107.766038

通过ROW_NUMBER()按name分组生成行号后，需根据以下规则为name字段追加后缀，最终返回id、修改后的name、latitude、longitude四列：

• 行号no=1时追加_A
• 行号no=2时追加_B
• 其余情况追加_C

可以通过CTE（公共表表达式）先计算行号，再在主查询中用CASE语句完成字符串拼接，SQL代码如下：

WITH numbered_data AS (
    SELECT 
        id, 
        name, 
        latitude, 
        longitude,
        ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) AS no
    FROM data
)
SELECT 
    id,
    CASE 
        WHEN no = 1 THEN name || '_A'
        WHEN no = 2 THEN name || '_B'
        ELSE name || '_C'
    END AS name,
    latitude,
    longitude
FROM numbered_data;

执行后得到的结果如下：

id,     name,        latitude,     longitude  
S226,   Farm 1_A,   -87.511372,    176.831784 
S011,   Farm 1_B,    -3.978185,    -173.035526
S540,   Farm 10_A,   20.242715,    -119.461222
S795,   Farm 10_B,   -49.680679,   107.766038 

注：若你的SQL方言不支持||进行字符串拼接，可替换为对应函数：比如MySQL用CONCAT(name, '_A')，SQL Server用name + '_A'。

内容的提问来源于stack exchange，提问作者Jetro Olowole