了解递归CTE终止检查

Question

在这个sqlfiddle中…

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

我收到以下错误…。

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

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

我只是想了解1）为什么根本需要它……毕竟每个订单行都与每个客户行都有关系，2）由于需要“终止检查”，因此该示例看起来像什么？工作。

顺便说一句，我想为该查询看到的输出如下。

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

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

编辑

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

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

Answer 1

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

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

对于您的查询，一个简单的联接就足够了。

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

但是，我看到您正在尝试以一种奇数格式返回此值，应在所使用的任何报表应用程序中对其进行处理。这将使您关闭而无需递归。

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