如何在SELECT查询中递归查找符合Rxxxxx格式的SerienNr

现有数据表PSAPacking_Det（结构及数据如下）：

需求：输入一个PackingNr，需查询出最终对应的符合Rxxxxx格式的SerienNr（排除PNxxxxx格式）。例如输入PN185971时，应得到R005478。

当前尝试用CASE语句实现，但由于无法确定递归查找的次数，该方案不可行。现有查询还需关联其他表并查询其他字段，尝试的SQL如下：

SELECT 
    ...  ,
    CASE 
        WHEN PSPD.SerienNr LIKE '%PN%' 
            THEN 
                (SELECT SerienNr FROM PSAPacking_Det 
                 WHERE PSAPacking_Det.PackingNr = PSPD.SerienNr) 
            ELSE PSPD.SerienNr 
    END AS SerienNr
    ...
FROM 
    PSAPacking PSPD
JOIN 
     ...
WHERE 
   PSPD.PackingNr = 'PN185971'

请问如何在SELECT查询中实现该需求？

可以使用**递归CTE（Common Table Expression）**实现这种不确定层数的递归查找，找到最终符合Rxxxxx格式的SerienNr后，再与原有查询关联获取所需字段。

具体实现代码 #

WITH RecursiveSerien AS (
    -- 锚点查询：获取输入PackingNr对应的初始记录
    SELECT 
        PackingNr, 
        SerienNr,
        1 AS Level
    FROM PSAPacking_Det
    WHERE PackingNr = 'PN185971' -- 替换为目标PackingNr，或用参数传递
    
    UNION ALL
    
    -- 递归查询：若当前SerienNr为PN开头，继续向下查找
    SELECT 
        r.PackingNr, -- 保留初始PackingNr，方便后续关联
        pd.SerienNr,
        r.Level + 1 AS Level
    FROM RecursiveSerien r
    JOIN PSAPacking_Det pd ON pd.PackingNr = r.SerienNr
    WHERE r.SerienNr LIKE 'PN%' -- 仅当当前SerienNr是PN格式时继续递归
)
-- 关联原有查询，输出最终结果
SELECT 
    -- 替换为你需要查询的其他表字段
    other_tables.*,
    rs.SerienNr AS FinalSerienNr
FROM 
    PSAPacking PSPD
JOIN 
    -- 保留你的原有关联表逻辑
    ...
JOIN 
    RecursiveSerien rs ON rs.PackingNr = PSPD.PackingNr
WHERE 
    PSPD.PackingNr = 'PN185971'
    AND rs.SerienNr LIKE 'R%'; -- 筛选出最终符合R格式的结果

关键说明 #

1. 递归CTE分为两部分：
   • 锚点成员：获取输入PackingNr对应的第一条记录，作为递归的起点；
   • 递归成员：将当前记录的SerienNr作为新的PackingNr继续查询，直到SerienNr不再是PN开头。
2. 最终通过rs.SerienNr LIKE 'R%'筛选出目标结果，确保得到的是符合格式的最终值。
3. 若需批量处理多个PackingNr，可移除锚点查询中的WHERE PackingNr = 'xxx'，改为在最终查询中筛选，递归CTE会自动处理每个PackingNr的关联链。

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