如何从多列提取分组唯一记录并替换plan_ASSO指定内容？

问题背景 #

现有一张包含plan_Id、plan_CD、plan_ASSO字段的数据表，原始数据如下：

需要完成两个操作：

1. 按plan_Id分组，每组保留一条记录，优先选择不带_H后缀的条目；若组内所有记录都带_H，则保留第一条；
2. 将plan_ASSO字段中第二个-后的数字替换为xx（例如A-2-41557-425898变为A-xx-41557-425898）。

最终期望结果 #

SQL解决方案（以MySQL为例） #

合并实现语句 #

直接一步完成筛选和替换：

SELECT 
    plan_Id, 
    plan_CD,
    CONCAT(
        SUBSTRING_INDEX(plan_ASSO, '-', 1), 
        '-xx-', 
        SUBSTRING_INDEX(plan_ASSO, '-', -2)
    ) AS plan_ASSO
FROM (
    SELECT 
        plan_Id, 
        plan_CD, 
        plan_ASSO,
        ROW_NUMBER() OVER (
            PARTITION BY plan_Id 
            ORDER BY CASE WHEN plan_ASSO NOT LIKE '%_H' THEN 0 ELSE 1 END, plan_ASSO
        ) AS rn
    FROM your_table_name
) t
WHERE rn = 1;

逻辑拆解 #

1. 分组筛选目标记录：

   • 用PARTITION BY plan_Id按plan_Id分组；
   • ORDER BY CASE WHEN plan_ASSO NOT LIKE '%_H' THEN 0 ELSE 1 END确保不带_H的记录排在组内最前，ROW_NUMBER()会给这类记录分配rn=1；
   • 若组内没有不带_H的记录，则按plan_ASSO排序取第一条。

2. 字段替换处理：

   • SUBSTRING_INDEX(plan_ASSO, '-', 1)截取第一个-前的内容（即A）；
   • SUBSTRING_INDEX(plan_ASSO, '-', -2)截取倒数两个-之后的所有内容（如41557-425898）；
   • 用CONCAT拼接成A-xx-xxxx-xxxx的格式。

其他数据库适配 #

• SQL Server：可替换字符串处理部分为LEFT(plan_ASSO, CHARINDEX('-', plan_ASSO)) + 'xx-' + RIGHT(plan_ASSO, LEN(plan_ASSO) - CHARINDEX('-', plan_ASSO, CHARINDEX('-', plan_ASSO)+1))；
• PostgreSQL：使用SPLIT_PART函数，拼接语句为CONCAT(SPLIT_PART(plan_ASSO, '-', 1), '-xx-', SPLIT_PART(plan_ASSO, '-', 3), '-', SPLIT_PART(plan_ASSO, '-', 4))（若带_H则需调整，可结合REGEXP_REPLACE更灵活）。

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