SQL中如何按Seq_Number合并字段值生成各ID完整流转路径？

要实现每行显示完整流转路径的需求，你可以借助窗口函数结合字符串聚合来完成。以下是基于PostgreSQL的实现方案（如果是其他数据库，只需替换对应的字符串聚合函数即可）：

完整SQL代码 #

WITH changelog_with_seq AS (
    SELECT 
        ac."id_Parent_Record",
        ac."Field_Changed",
        ac."Value_Previous",
        ac."Value_New",
        ac."Changed_Date",
        ROW_NUMBER() OVER (
            PARTITION BY "id_Parent_Record", "Field_Changed" 
            ORDER BY "Changed_Date" ASC
        ) AS "Seq_Number"
    FROM public.api_changelog ac
    WHERE ac."Field_Changed" IN ('_id_Matter','Task_Workflow')
      AND ac."id_Parent_Record" = '637454609993200'
)
SELECT 
    *,
    STRING_AGG("Value_New", ' -> ') OVER (
        PARTITION BY "id_Parent_Record", "Field_Changed" 
        ORDER BY "Seq_Number" ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS "Transitions"
FROM changelog_with_seq;

代码说明 #

1. CTE部分：保留你原有的逻辑，生成带序号Seq_Number的数据集。
2. 字符串聚合：使用STRING_AGG函数将同组（id_Parent_Record+Field_Changed）内的Value_New按Seq_Number顺序拼接，分隔符用' -> '。
3. 窗口范围：ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING指定取当前分组内的所有行，确保每行都能显示完整的流转路径，而非仅累积到当前行的路径。

其他数据库适配 #

• 若使用MySQL 8.0+，将STRING_AGG替换为GROUP_CONCAT，调整后代码如下：

  WITH changelog_with_seq AS (
      SELECT 
          ac.id_Parent_Record,
          ac.Field_Changed,
          ac.Value_Previous,
          ac.Value_New,
          ac.Changed_Date,
          ROW_NUMBER() OVER (
              PARTITION BY id_Parent_Record, Field_Changed 
              ORDER BY Changed_Date ASC
          ) AS Seq_Number
      FROM api_changelog ac
      WHERE ac.Field_Changed IN ('_id_Matter','Task_Workflow')
        AND ac.id_Parent_Record = '637454609993200'
  )
  SELECT 
      *,
      GROUP_CONCAT(Value_New ORDER BY Seq_Number SEPARATOR ' -> ') OVER (
          PARTITION BY id_Parent_Record, Field_Changed
      ) AS Transitions
  FROM changelog_with_seq;
  

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