SQL技术求助：处理一对多数据，新增Column D存储次值并清理Column A

需求分析 #

• 去除Column A末尾的字母，保留数字部分
• 按处理后的Column A分组，每组仅保留第一条Column C记录，同时将组内第二条Column C值存入新增的Column D，无第二条则留空
• Column A与Column B为1:1关联，确保处理后每个Column A对应唯一的Column B

输入数据表 #

解决方案 #

使用窗口函数ROW_NUMBER()为每个原始Column A分组的行编号，再通过自连接获取组内第二条Column C的值，同时处理Column A的格式：

WITH ranked_data AS (
    SELECT 
        ColumnA,
        ColumnB,
        ColumnC,
        -- 按原始Column A分组，对Column C排序后编号
        ROW_NUMBER() OVER (PARTITION BY ColumnA ORDER BY ColumnC) AS row_num
    FROM your_table_name -- 替换为你的实际表名
)
SELECT
    -- 去除Column A末尾的字母，正则适配任意末尾字母
    REGEXP_REPLACE(r1.ColumnA, '[a-zA-Z]$', '') AS ColumnA,
    r1.ColumnB,
    r1.ColumnC,
    -- 左连接获取组内第二条Column C，无则为空
    r2.ColumnC AS ColumnD
FROM ranked_data r1
LEFT JOIN ranked_data r2 
    ON r1.ColumnA = r2.ColumnA 
    AND r2.row_num = 2
WHERE r1.row_num = 1;

代码说明 #

• CTEranked_data：给每个原始Column A分组的行按Column C排序并分配行号，组内第一行编号为1，第二行为2
• Column A格式处理：REGEXP_REPLACE函数匹配末尾的单个字母并移除；若数据库不支持正则（如低版本SQL Server），可替换为对应方言的字符串截取函数：
  • SQL Server：LEFT(r1.ColumnA, LEN(r1.ColumnA)-1)
  • MySQL：SUBSTRING(r1.ColumnA, 1, CHAR_LENGTH(r1.ColumnA)-1)
• Column D取值：通过左连接关联同一分组内行号为2的记录，若不存在该记录，Column D自动返回NULL（对应报表中的空值）
• 结果筛选：仅保留每个分组内行号为1的记录，确保每组输出唯一一行

期望输出 #

内容的提问来源于stack exchange，提问作者Krystal Lindquist