mysql - SQL 输出呈现重命名的重复条目

Question

我有一个包含类似于以下数据的表:

id  p_id   process
300 1      initial
300 2      stage_beta
300 3      conversion
300 4      render
300 5      stage_beta
300 6      conversion
300 7      render
300 8      finish

我想生成一个 SQL 查询，以便我的输出将重复进程修改为一个新进程。

id  p_id    process
300 1   initial
300 2   stage_beta
300 3   conversion
300 4   render
300 5   stage_beta_new
300 6   conversion_new
300 7   render_new
300 8   finish

我已经尝试使用以下连接的变体，但我遗漏了一些东西:

SELECT id,
       process,
       process_id
FROM process_view

JOIN (SELECT id, 
             process_id,
             (CASE WHEN process = "stage_beta" then "stage_beta_new"
                   WHEN process = "conversion" then "conversion_new"
                   WHEN process = "render" then "render_new"
              ELSE name
              END) as name
      FROM process_view) AS process2 on process.id = process2.id

最终结果只是重命名所有字段，但不会保留初始过程，也不会考虑第三次重复该过程。

如有任何建议或建议，我们将不胜感激。

Answer 1

首先给每个重复的过程分配一个编号:

 SELECT id,  p_id, process,
        row_number() over (partition by id, process order by p_id) as rn
 FROM YourTable

现在你可以给第一个以外的所有分配新的，或者分配数字:

SELECT id,  p_id,
       CASE WHEN rn > 1 
            THEN CONCAT(process, '_new')
            ELSE process
       END as process,
       CONCAT(process, '_' , rn) as process2
FROM (           
     SELECT id,  p_id, process,
            row_number() over (partition by id, process order by p_id) as rn
     FROM YourTable
)