请求协助：基于双表数据创建带系数运算的递归CTE

没问题，我来帮你搞定这个递归查询。首先咱们先把B表的示例数据补全（因为你提到每个Site对应多条系数记录），假设是每个Site后续年份的递增系数，这样能更直观地演示递归逻辑：

-- 补全B表的示例数据
insert into B values 
('A', 1.05, 2004), ('A', 1.03, 2005), ('A', 1.02, 2006),
('B', 1.04, 2003), ('B', 1.06, 2004),
('C', 1.07, 2002), ('C', 1.01, 2003), ('C', 1.04, 2004)

接下来分两种常见场景给出实现方案：

场景1：B表年份连续递增（每年一条系数） #

如果B表的年份是和T表的初始年份连续衔接的（比如T表是2003，B表从2004开始每年一条），可以直接按年份关联递归：

WITH RecursivePrice AS (
    -- 锚点成员：取T表的初始价格作为递归起点
    SELECT 
        Site,
        Price AS CurrentPrice,
        Year AS CurrentYear
    FROM T
    UNION ALL
    -- 递归成员：用当前价格乘以对应年份的系数，得到下一年的价格
    SELECT 
        rp.Site,
        rp.CurrentPrice * b.Coeff AS CurrentPrice,
        b.Year AS CurrentYear
    FROM RecursivePrice rp
    JOIN B b 
        ON rp.Site = b.Site
        -- 匹配当前年份的下一年系数，若要计算历史年份则改为 b.Year = rp.CurrentYear - 1
        AND b.Year = rp.CurrentYear + 1
)
-- 按站点和年份排序输出结果
SELECT * FROM RecursivePrice ORDER BY Site, CurrentYear;

关键说明： #

• 锚点成员直接获取T表的唯一初始记录，这是递归的基础
• 递归成员通过JOIN匹配相同站点、且年份为当前年份+1的系数，自动终止条件是当某个站点没有下一年的系数时，递归就会停止
• 如果需要计算历史年份（比如从T表的2003年倒推2002、2001年的价格），只需要把b.Year = rp.CurrentYear + 1改成b.Year = rp.CurrentYear - 1即可

场景2：B表年份不连续（存在间隔） #

如果B表的年份不是连续的（比如某站点有2004、2006的系数，但没有2005），直接按年份+1关联会漏掉数据，这时候可以先给每个站点的系数按年份排序，再按序号递推：

WITH BOrdered AS (
    -- 先给每个站点的系数按年份升序编号
    SELECT 
        Site,
        Coeff,
        Year,
        ROW_NUMBER() OVER (PARTITION BY Site ORDER BY Year) AS Seq
    FROM B
),
RecursivePrice AS (
    -- 锚点成员：初始序号设为0，对应B表的序号从1开始
    SELECT 
        Site,
        Price AS CurrentPrice,
        Year AS CurrentYear,
        0 AS CurrentSeq
    FROM T
    UNION ALL
    -- 递归成员：按序号依次匹配系数计算价格
    SELECT 
        rp.Site,
        rp.CurrentPrice * bo.Coeff AS CurrentPrice,
        bo.Year AS CurrentYear,
        bo.Seq AS CurrentSeq
    FROM RecursivePrice rp
    JOIN BOrdered bo 
        ON rp.Site = bo.Site
        AND bo.Seq = rp.CurrentSeq + 1
)
SELECT * FROM RecursivePrice ORDER BY Site, CurrentYear;

关键说明： #

• 先用ROW_NUMBER()给每个站点的系数按年份排序，生成连续的序号，避免年份不连续导致的递归中断
• 递归时按序号+1匹配，不管年份是否连续，都能按顺序计算所有系数对应的价格

注意事项 #

• 要避免递归循环：确保B表的年份/序号是单向递增（或递减）的，不要出现循环引用（比如某站点的系数年份出现2004→2005→2004），否则会触发无限递归
• 如果需要保留初始价格、累计系数等信息，可以在CTE中添加额外字段（比如InitialPrice, TotalCoeff）来跟踪

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