T-SQL实现关联所有前置行的递归查询方案

初始条件：假设有100人食用坚果、糖果、苹果三类食物，三类食物的总库存上限各为1000份。所有人员每日分2餐进食，每人每餐进食的三类食物分量各不相同，且所有人第一餐的三类食物进食量为已知值。

计算需求：

• 每人单日单类食物的进食总量上限为10份，人员按固定顺序轮流进食，需要计算每位人员完成进食后，三类食物分别剩余的库存数量
• 结合第一餐进食量、单日单类10份的上限、剩余库存，计算每位人员第二餐可进食的三类食物最大分量

用户初步判断该需求需要通过递归实现，每轮迭代需要同时引用当前行与所有历史前置行数据，也希望了解是否存在其他更优的实现方式。

现有测试表构造语句如下：

select 1 num, 1 s,  50 ss, 11 b, 20 bs
union
select 2 num, 1 s, 101 ss, 11 b, 50 bs
union
select 3 num, 2 s, 103 ss, 12 b, 50 bs

期望按num字段顺序逐轮迭代，每轮迭代将当前行与所有前置行（含当前行自身）做关联输出，各轮迭代的期望输出结构如下：

1 迭代（num=1）：
[n  num s   ss  b   bs  num_    s_  ss_ b_  bs_
1   1   1   50  11  20  1   1   50  11  20]

2 迭代（num=2）：
[n  num s   ss  b   bs  num_    s_  ss_ b_  bs_
2   2   1   101 11  50  2   1   50  11  20
2   2   1   101 11  50  2   1   101 11  50]

3 迭代（num=3）：
[n  num s   ss  b   bs  num_    s_  ss_ b_  bs_
3   3   2   103 12  50  3   1   50  11  20
3   3   2   103 12  50  3   1   101 11  50
3   3   2   103 12  50  3   2   103 12  50]

后续将在每轮迭代中加入业务计算逻辑，因此需要先实现上述关联结构。

原有递归CTE逻辑存在缺陷：递归部分仅继承上一轮的历史行数据，未将当前迭代行加入历史集合，无法输出当前行与自身的关联记录，结果不符合预期。

修正后的递归实现 #

with a as  
(
    select 1 num, 1 s,  50 ss, 11 b, 20 bs
    union
    select 2 num, 1 s, 101 ss, 11 b, 50 bs
    union
    select 3 num, 2 s, 103 ss, 12 b, 50 bs
)
,
rec as (
    -- 锚点：num=1时仅关联自身
    select 1 n, a.num, a.s, a.ss, a.b, a.bs, a.num num_, a.s s_, a.ss ss_, a.b b_, a.bs bs_  
    from a where num =1
    union all
    -- 递归部分1：继承上一轮所有历史行与当前行的关联
    select n+1, curr.num, curr.s, curr.ss, curr.b, curr.bs, hist.num_ , hist.s_, hist.ss_, hist.b_, hist.bs_ 
    from rec hist
    join a curr on curr.num = hist.n +1
    union all
    -- 递归部分2：加入当前行与自身的关联记录
    select n+1, curr.num, curr.s, curr.ss, curr.b, curr.bs, curr.num, curr.s, curr.ss, curr.b, curr.bs
    from rec hist
    join a curr on curr.num = hist.n +1
    where hist.n = curr.num -1
)
select * from rec 
order by n,num, num_

更优非递归实现 #

该场景的关联逻辑本质是匹配所有num_ <= 当前num的行对，完全不需要递归，直接通过非等值自连接即可实现，代码更简洁，数据量较大时性能远高于递归写法，后续叠加业务计算逻辑也更方便：

with a as  
(
    select 1 num, 1 s,  50 ss, 11 b, 20 bs
    union
    select 2 num, 1 s, 101 ss, 11 b, 50 bs
    union
    select 3 num, 2 s, 103 ss, 12 b, 50 bs
)
select 
    curr.num as n,
    curr.num, curr.s, curr.ss, curr.b, curr.bs,
    hist.num as num_, hist.s as s_, hist.ss as ss_, hist.b as b_, hist.bs as bs_
from a curr
join a hist on hist.num <= curr.num
order by curr.num, hist.num

运行上述代码可直接得到与预期完全一致的输出结构。

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