如何在递归CTE中调用表值函数（TVF）？

创建了sales表存储销售数据：

create table sales (
    date date,
    total decimal(6,2)
);

同时定义了表值函数ds(@date date)，用于查询指定日期的销售记录：

create function ds(@date date) returns table as
return select date, total from sales where date=@date;

尝试用递归CTE生成日期序列，并调用ds函数查询对应日期的销售数据，编写的代码如下：

with cte as (
    select cast('2023-01-01' as date) as n, *
    from ds('2023-01-01')
    union all
    select dateadd(day,1,n),d.*
    from cte, ds(n) as d    -- 无法使用ds(n)或ds(cte.n)
    where n<'2023-01-05'
)
select * from cte order by n option(maxrecursion 100);

执行时报错：Invalid column name 'n'，CTE中生成的列n可以在SELECT子句正常使用，但无法作为ds()的参数传入。需要解决如何将递归生成的日期n传入表值函数的问题。

解决方案 #

问题出在SQL Server的解析逻辑上，递归CTE的递归部分中，不能直接在FROM子句里将CTE的列作为表值函数的参数使用。需要用CROSS APPLY来实现表值函数与CTE行的关联，它会为CTE中的每一行调用一次表值函数，正确传递参数。

修正后的代码如下：

with cte as (
    select cast('2023-01-01' as date) as n, *
    from ds('2023-01-01')
    union all
    select dateadd(day,1,cte.n),d.*
    from cte
    cross apply ds(cte.n) as d
    where cte.n<'2023-01-05'
)
select * from cte order by n option(maxrecursion 100);

关键改动说明 #

• 将原来的from cte, ds(n) as d替换为from cte cross apply ds(cte.n) as d，明确指定从CTE的n列获取参数传递给ds函数
• 递归部分的dateadd和where条件中显式使用cte.n，避免列名歧义，提升代码可读性

这样修改后，递归CTE就能正常生成日期序列，并为每个日期调用ds函数获取对应的销售数据了。

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