主页 / dba / 问题 / 321136
Accepted
Rishav Ghosh
Asked: 2022-12-19 06:44:00 +0800 CST

使用递归 cte 生成日期

  • 772

我有一个表销售

product_id  period_start  period_end  sale
----------------------------------------
1          2019-01-25    2019-02-28   100
2          2018-12-01    2020-01-01   10
3          2019-12-01    2020-01-31   1

我想要一个输出：

product_id  period_start  period_end  sale
----------------------------------------
1          2019-01-25    2019-02-28   100
1          2019-01-26    2019-02-28   100
1          2019-01-27    2019-02-28   100
.               .            .         .
.               .            .         .
1          2019-02-28    2019-02-28   100
2          2018-12-01    2020-01-01   10
2          2018-12-02    2020-01-01   10
.               .            .         .
.               .            .         .
2          2020-01-01    2020-01-01   10
3          2019-12-01    2020-01-31   1
3          2019-12-02    2020-01-31   1
.               .            .         .
.               .            .         .
3          2020-01-31    2020-01-31   1

我尝试了以下但它似乎不起作用

with alldates as(
  select * from sales
  union all
  select product_id, dateadd(day, 1, period_start) as period_start, period_end, average_daily_sales
  from  alldates
  where period_start < period_end
)

有什么方法可以使用递归 cte 来实现吗？

动态链接库：

create table sales (
product_id int,
period_start date,
period_end date,
average_daily_sales int
);

insert into sales values(1,'2019-01-25','2019-02-28',100),(2,'2018-12-01','2020-01-01',10),(3,'2019-12-01','2020-01-31',1);
sql-server

1 个回答

  1. Best Answer

    只需添加option(maxrecursion 0)到您的决赛select

    with alldates as(
  select * from sales
  union all
  select product_id, dateadd(day, 1, period_start) as period_start, period_end, average_daily_sales
  from  alldates
  where period_start < period_end
)
SELECT *
FROM allDates
option(maxrecursion 0);

    小提琴手

    但是，我建议有一个日历表或一个理货表，并使用该实用程序表来编写查询。

    我master..spt_values 只是作为示例使用，但您应该使用实用程序表

    ;with tallyTable as 
(select number
  from master..spt_values
   where type ='p'
)
SELECT s.product_id,dateadd(day,tt.number, s.period_start) as period_start, s.period_end,s.average_daily_sales
FROM tallyTable as tt
     INNER JOIN sales as s
           on dateadd(day,tt.number, s.period_start)<= s.period_end
ORDER BY product_id,period_start

    在此链接中查看更多信息
    https://www.mssqltips.com/sqlservertip/5111/fix-sql-server-cte-maximum-recursion-exhausted-error/
    对于tally table
    https://www.sqlservercentral.com/blogs/tally -表-in-t-sql

    https://sqlbenjamin.wordpress.com/2018/02/06/sql-tip-the-tally-table/

    • 2

相关问题