了解递归CTE终止检查 [英] Understanding the recursive CTE termination check

问题描述

在此sqlfiddle中...

In this sqlfiddle...

http://sqlfiddle.com/#!6/b6587/6

我遇到以下错误...。

I am getting the following error....

该语句终止。在语句完成之前，最大递归100已用尽
。

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

我知道需要进行终止检查在CTE第二选择的where子句中。即使您取消注释WHERE子句，我也会遇到相同的错误。

I understand that there needs to be a "termination check" in the where clause of the second select of the CTE. Even if you uncomment the WHERE clause I get the same error.

我只是想了解1）为什么根本需要它……毕竟每个订单行与每个客户行都有关系，2）因为 终止检查是必需的，此示例才能正常工作。

I am just trying to understand 1) why it is needed at all...after all each order row has a relationship to each customer row and 2) since a "termination check" IS required what would that look like to get this example to work.

BTW，我希望此查询看到的输出如下。

BTW, the output I would like to see for this query is as follows.

1,'George', 'Patton','',''
1,'','','<some date>', 'tank'
1,'','','<some date>', 'plane'
2,'Lewie', 'Puller','',''
2,'','','<some date>', 'Rifle'
2,'','','<some date>', 'Hand Grenade'

顺便说一句，随意评论其他获得这种结果的方法（无需使用递归CTE），但也无需使用游标或临时表。我想通过基于集合的操作来做到这一点。

By the way, feel free to comment on other ways to get this kind of result (without using a recursive cte) but also without using a cursor or temp table. I would like to do this with a set-based operation.

编辑

根据记录，我知道联接会起作用，但是我有理由询问递归。为了给您提供背景信息，我正在研究一个EXPORT文件的结果集。每行只有一列，称为LINE。此外，导出文件需要按照我的示例顺序输出...详细信息需要在标题行下，并且该模式需要重复... header / details ... header / details。我认为也许递归可以解决这个问题。但是我知道简单的联接也可以解决问题，只要我可以将行按正确的顺序...标题/详细信息...标题/详细信息，也许可以通过某种方式来对输出中的数字进行排序正确的方法。

For the record, I know a join would work but I had a reason for asking about recursion. To give you context, I am working on a result set that is an EXPORT file. Each row is only one column called LINE. Furthermore, the export file needs to output in the order of my example...details need to be under header rows and that pattern needs to repeat...header/details ... header/details. I thought that maybe recursion could solve this problem. But I know simple join could solve it too as long as I can get the rows into the correct order...header / detail ... header / detail, maybe by having some way to do a column for sorting that numbers the output in the right way.

我的导出将不依赖于调用应用程序来格式化数据...存储过程需要格式化数据。

My export will NOT be depending on the calling app to format the data...the stored procedure needs to format the data.

推荐答案

以下是使用日期的更好示例。假设我们要建立一个日期表。 2017年每个月有1行。我们创建 @startDate 作为锚点，并创建 @endDate 作为终止符。我们希望将它们间隔12个月，因为我们需要一年。然后，递归将通过 DATEADD 函数添加一个月到 @startDate ，直到在 WHERE 子句。我们知道需要12次递归才能达到12个月……即11个月+开始日期。如果我们将 MAXRECURSION 设置为小于11的值，则它将失败，因为需要11个来满足条款在我们的递归 CTE 中，即终止符。

Here's a better example using dates. Assume we want to build a table of dates. 1 row for every month for the year 2017. We create a @startDate as the anchor and @endDate as the terminator. We set these to 12 months apart, since we want a single year. Then, the recursion will add one month via the DATEADD function to the @startDate until the terminator is met in the WHERE clause. We know it will take 11 recursions to hit 12 months... that is, 11 months + the start date. If we set the MAXRECURSION to anything less than 11, then it will fail since 11 are needed to fulfill the WHEREclause in our recursive CTE, that is the terminator..

declare @startDate datetime = '20170101'
declare @endDate datetime = '20171201'

;WITH Months
as
(
    SELECT @startDate as TheDate       --anchor
    UNION ALL
    SELECT DATEADD(month, 1, TheDate)  --recursive
    FROM Months
    WHERE TheDate < @endDate           --terminator... i.e. continue until this condition is met

)


SELECT * FROM Months OPTION (MAXRECURSION 10) --change this to 11

对于您的查询，简单的连接就足够了。

For your query, a simple join would suffice.

select 
  firstName
  ,lastName
  ,orderDate
  ,productID
from
  customers c
inner join
  orders o on o.customerID = c.id

但是，我看到您正在尝试以一种奇数格式返回此值，该格式应在您使用的任何报表应用程序中进行处理。

However, I see that you are trying to return this in an odd format, which should be handled in what ever reporting application you are using. This would get you close without recursion.

with cte as(
select 
  firstName
  ,lastName
  ,orderDate
  ,productID
  ,dense_rank() over(order by c.id) as RN
from
  customers c
inner join
  orders o on o.customerID = c.id)


select distinct
  firstName
  ,lastName
  ,null
  ,null
  ,RN
from 
  cte
union all
select
  ''
  ,''
  ,orderDate
  ,productID
  ,RN
from 
  cte
order by RN, firstName desc

这篇关于了解递归CTE终止检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！