SQL Server:重写递归CTE以替换视图中的选项maxrecursion [英] SQL Server: Rewrite recursive CTE to substitute for option maxrecursion in a View

问题描述

我遇到了一个问题，我们不能在视图内部的查询下使用，而只能在表中使用.不幸的是，在这种情况下，我们没有表格作为该项目的选择.

我很好奇，是否有人知道我应该寻找哪个方向来代替基本逻辑:

我想做的是通过执行以下查询来为日期范围内的每一天创建一条记录:

 与CTE_PerDay AS(选择TableDateRange.objectId，TableDateRange.amount，TableDateRange.beginDate，COALESCE(TableDateRange.endDate，'2099-12-31')AS endDate从TableDateRange全联盟选择CTE_PerDay.objectId，CTE_PerDay.amount，DATEADD(DAY，1，CTE_PerDay.beginDate)AS beginDate，CTE_PerDay.endDate从CTE_PerDay在哪里GETDATE()>DATEADD(DAY，1，CTE_PerDay.beginDate))SELECT * FROM CTE_PerDay选项(最大递归0) 

样本数据集 TableDataRange

<身体>

ObjectId	Amount	beginDate	endDate
1	500	2020-01-03
2	35	2015-05-31	2019-10-01
3	200	2017-03-15	2020-06-02

  CREATE TABLE TableDateRange(ObjectId varchar(300)，金额int，beginDate日期，endDate日期);插入TableDateRange(ObjectId，Amount，beginDate，endDate)价值('1'，500，'2020-01-03'，NULL)，('2'，35，'2015-05-31'，'2019-10-01')，('3'，200，'2017-03-15'，'2020-06-02'); 

因此查询运行正常，但是在视图中，我无法使用 OPTION 功能，如果没有它，我将收到错误消息语句终止.在语句完成之前，最大递归100已用尽.有什么建议吗?

解决方案

您可以使用提示:这是一个基于集合的解决方案，当迭代次数增加时，其性能优于递归-并且视图中也支持./p>

这是一种方法:

 选择t.objectid，t.amount，dateadd(day，x.n，t.begindate)作为dt从 (在(按(选择null)排序)上选择row_number()-1从(values(0)，(0)，(0)，(0)，(0)，(0)，(0)，(0)，(0)，(0))a(n)交叉连接(值(0)，(0)，(0)，(0)，(0)，(0)，(0)，(0)，(0)，(0))b [n)交叉连接(值(0)，(0)，(0)，(0)，(0)，(0)，(0)，(0)，(0)，(0))c(n))x(n)内部联接表数据范围t在dateadd(day，x.n，t.begindate)< =大小写当结束日期< = convert(date，getdate())然后结束日期否则convert(date，getdate())结尾 

计数会生成0到999之间的所有数字(您可以通过添加 cross join s轻松扩展它).我们使用它来相乘".原始表格的行并生成日期范围.

我试图重写处理结束日期的部分.我了解您不希望将来的日期，所以 on 子句中的条件就是这样.

对于此示例数据:

ObjectId |金额|beginDate |结束日期-------:|-----:|:--------- |:---------1 |500 |2020-12-28 |空2 |35 |2019-09-26 |2019-10-013 |200 |2020-05-28 |2020-06-02

查询返回:

objectid |金额|dt-------:|-----:|:---------1 |500 |2020-12-281 |500 |2020-12-291 |500 |2020-12-301 |500 |2020-12-312 |35 |2019-09-262 |35 |2019-09-272 |35 |2019-09-282 |35 |2019-09-292 |35 |2019-09-302 |35 |2019-10-013 |200 |2020-05-283 |200 |2020-05-293 |200 |2020-05-303 |200 |2020-05-313 |200 |2020-06-013 |200 |2020-06-02

DB Fiddle上的演示

>

I ran into the issue that we cannot use underneath query inside a view, but only in a table. Unfortunately we deal with the situation that we don't have tables as an option for this project.

I am curious whether someone knows in what direction I should look for to substitute for underneath logic:

What I try to do is create a record for each day within a date range, by doing the following query:

WITH CTE_PerDay AS (
    SELECT 
         TableDateRange.objectId
        ,TableDateRange.amount
        ,TableDateRange.beginDate
        ,COALESCE(TableDateRange.endDate, '2099-12-31') AS endDate
    FROM TableDateRange
    UNION ALL
    SELECT
         CTE_PerDay.objectId
        ,CTE_PerDay.amount
        ,DATEADD(DAY, 1, CTE_PerDay.beginDate) AS beginDate
        ,CTE_PerDay.endDate
    FROM CTE_PerDay 
    WHERE GETDATE() > DATEADD(DAY, 1, CTE_PerDay.beginDate)

)
SELECT * FROM CTE_PerDay
OPTION (maxrecursion 0)

Sample Dataset TableDataRange

ObjectId	Amount	beginDate	endDate
1	500	2020-01-03
2	35	2015-05-31	2019-10-01
3	200	2017-03-15	2020-06-02

CREATE TABLE TableDateRange
(
     ObjectId   varchar(300),
     Amount     int,
     beginDate  date,
     endDate    date
);

INSERT INTO TableDateRange ( ObjectId , Amount , beginDate , endDate )
VALUES
    ('1', 500, '2020-01-03', NULL),
    ('2', 35, '2015-05-31', '2019-10-01'),
    ('3', 200, '2017-03-15', '2020-06-02');

So the query runs fine, however in a view I can't use the OPTION functionality, and without it I get the error 'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.' Any suggestions?

解决方案

You could use a tally: that's a set-based solution, that performs better than recursion when the number of iterations increases - and it is supported in views.

Here is an approach:

select t.objectid, t.amount, dateadd(day, x.n, t.begindate) as dt
from (
    select row_number() over (order by (select null)) - 1
    from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
) x(n)
inner join tabledatarange t
    on dateadd(day, x.n, t.begindate) <= case 
        when enddate <= convert(date, getdate()) then enddate
        else convert(date, getdate())
    end

The tally generates all numbers between 0 and 999 (you can easily expand it by adding cross joins). We use it to "multiply" the rows of the original table and generate the date range.

I attempted to rewrite the part the handles the end date. I understand that you don't want future dates, so that's what the condition in the on clause does.

For this sample data:

ObjectId | Amount | beginDate  | endDate   
-------: | -----: | :--------- | :---------
       1 |    500 | 2020-12-28 | null      
       2 |     35 | 2019-09-26 | 2019-10-01
       3 |    200 | 2020-05-28 | 2020-06-02

The query returns:

objectid | amount | dt        
-------: | -----: | :---------
       1 |    500 | 2020-12-28
       1 |    500 | 2020-12-29
       1 |    500 | 2020-12-30
       1 |    500 | 2020-12-31
       2 |     35 | 2019-09-26
       2 |     35 | 2019-09-27
       2 |     35 | 2019-09-28
       2 |     35 | 2019-09-29
       2 |     35 | 2019-09-30
       2 |     35 | 2019-10-01
       3 |    200 | 2020-05-28
       3 |    200 | 2020-05-29
       3 |    200 | 2020-05-30
       3 |    200 | 2020-05-31
       3 |    200 | 2020-06-01
       3 |    200 | 2020-06-02

Demo on DB Fiddle

这篇关于SQL Server:重写递归CTE以替换视图中的选项maxrecursion的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！