Sybase ASE存储过程SQL实现：链式追溯根ID获取最新数据

问题描述 #

现有Sybase表结构如下（id列按时间从旧到新排列）：

需要编写存储过程中的SQL代码，生成如下结果：

需求说明：id为2的记录经过3次修改（行4、行5、行6），需通过refId反向链式追溯找到原始id（2），并关联展示其最新数据（d6）；id为1和3的记录无关联修改记录，直接保留当前数据。

环境信息 #

• 服务器：Adaptive Server Enterprise/16.0 SP03 PL08
• 驱动URL：jdbc:jtds:sybase
• 驱动：Sybase jTDS

解决方案 #

利用Sybase ASE 16.0支持的递归CTE（公共表表达式）遍历refId关联链条，定位每条原始记录对应的最新版本数据，SQL代码如下：

WITH RECURSIVE record_chain AS (
    -- 锚点成员：取出所有无修改的原始记录
    SELECT 
        id AS original_id,
        id AS current_id,
        data,
        refId
    FROM your_table_name
    WHERE refId IS NULL

    UNION ALL

    -- 递归成员：沿refId链条追溯所有后续修改记录
    SELECT 
        rc.original_id,
        t.id AS current_id,
        t.data,
        t.refId
    FROM record_chain rc
    JOIN your_table_name t ON rc.current_id = t.refId
)
-- 筛选每个原始记录的最新版本（current_id最大的条目）
SELECT 
    original_id AS id,
    data
FROM (
    SELECT 
        original_id,
        data,
        ROW_NUMBER() OVER (PARTITION BY original_id ORDER BY current_id DESC) AS rn
    FROM record_chain
) temp
WHERE rn = 1
ORDER BY original_id;

代码说明 #

1. 递归CTE record_chain：
   • 锚点部分先提取所有refId为NULL的原始记录，标记original_id为自身id；
   • 递归部分通过关联当前记录的current_id与下一条记录的refId，持续追溯链条上的所有修改记录，同时保留原始id。
2. ROW_NUMBER()窗口函数：按原始id分组，以current_id降序排序，取每组第一条（即最新修改的记录）。
3. 结果输出：得到每个原始id对应的最新data，按id排序展示。

注意：将代码中的your_table_name替换为实际表名。

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