如何用SQL获取所有记录的Root Source（根来源）

针对层级不固定（3条或10条记录）导致Lead/Lag函数失效的问题，**递归CTE（公共表表达式）**是最适配的解决方案，它能遍历任意深度的证书关联链，准确获取每条记录的根来源。

表结构回顾 #

解决方案SQL代码 #

WITH RecursiveCert AS (
    -- 锚点查询：定位所有根证书（无旧证书的记录）
    SELECT 
        CustID,
        CertNo,
        OldCertNo,
        Source,
        Source AS RootSource
    FROM YourTableName
    WHERE OldCertNo IS NULL OR OldCertNo = ''

    UNION ALL

    -- 递归查询：关联子证书，继承根来源
    SELECT 
        c.CustID,
        c.CertNo,
        c.OldCertNo,
        c.Source,
        rc.RootSource
    FROM YourTableName c
    INNER JOIN RecursiveCert rc 
        ON c.OldCertNo = rc.CertNo
)
-- 输出所有记录的根来源
SELECT 
    CustID,
    CertNo,
    OldCertNo,
    Source,
    RootSource AS [Root Source]
FROM RecursiveCert
ORDER BY CustID, CertNo;

代码逻辑说明 #

1. 锚点成员：筛选出所有没有父证书（OldCertNo为空或空字符串）的记录，这些记录的RootSource就是自身的Source。
2. 递归成员：将每一条子证书（OldCertNo关联父证书的CertNo）与递归CTE中的父记录关联，直接继承父记录的RootSource，以此类推遍历完整证书链。
3. 最终查询：从递归CTE中取出所有记录，即可得到每条记录对应的根来源，不受层级数量限制。

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