在递归CTE中使用表值函数

Question

我正在尝试使用递归CTE来组合对表值函数的多个调用。

我有一个小提琴在:https://dbfiddle.uk/kktqHivz

我有一个名为sales:

的表

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

函数ds(date)选择日期的所有销售额:

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

我想我可以将它们放入递归CTE中，如下所示:

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    --  can’t use ds(n) or ds(cte.n)
where n<'2023-01-05'
)
select * from cte order by n option(maxrecursion 100);

但是我得到的信息是:

无效的列名'n'

我在这一点上卡住了。n是来自CTE的一列，通常您可以在递归成员中使用它，正如您在SELECT子句中看到的那样。不知何故，我不能用它作为ds()TVF的参数。

有一种方法，我可以使用生成的日期n表值函数?

Answer 1

正如另一个答案所提到的，您需要cross apply。

但是你的CTE逻辑是不正确的。您需要首先生成日期，然后apply函数

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

话虽如此，您可能应该使用直接范围查找

select
date,
total
from sales
where date >= @date
and date < '2023-01-05';

Answer 2

这个from cte, ds(n) as d是一个老式的逗号连接，所以ds当然不能引用cte中的列。相反，可以尝试cross apply，它相当于需要从基表中获取值的函数的连接。

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
cross apply ds(n) as d
where n < '2023-01-05'
)
select *
from cte
order by n
option(maxrecursion 100);