为什么我们不能在递归CTE中使用外部联接？ [英] Why cannot we use outer joins in Recursive CTE?

问题描述

请考虑以下内容

 ;与GetParentOfChild AS 
（
 SELECT 
 Rn = ROW_NUMBER（）超过（排序方式（选择1））
，row_id AS父母
，parent_account_id作为ParentId 
 FROM siebelextract..account 
 WHERE row_id = @ChildId 
 UNION ALL 
选择
 Rn + 1 
，a.row_id作为父母
，a.parent_account_id作为ParentId 
 FROM siebelextract..account a 
 JOIN GetParentOfChild gp on a.row_id = gp.ParentId 
）
 
选择TOP 1 @ChildId =父母
 FROM GetParentOfChild 
按Rn DESC命令排序
  

它的作用是给任何孩子，它将返回根级别的父级...。程序在所有时间...

出于好奇/实验的缘故，我将JOIN更改为Left Outer Join，它报告了

< p > 消息462，级别16，状态1，过程GetParent，第9行
不允许在递归公用表表达式 GetParentOfChild的递归部分中进行外部联接。

我的问题是为什么CTE的递归部分不能接受左外部联接？是设计使然吗？

谢谢

解决方案

是的通过设计，请阅读定义和使用递归公用表表达式的准则

在
递归成员的CTE_query_definition中不允许以下项目：

• 选择区域


• 组别


• 拥有生活


• 标量聚合


• TOP


• 左，右，外连接（允许内连接）


• 子查询

请注意，如果您的查询通过CTE自身进行左联接，则可以变为无限递归

Consider the below

;WITH GetParentOfChild AS
    (
        SELECT 
            Rn = ROW_NUMBER() Over(Order By (Select 1))
            ,row_id AS Parents
            ,parent_account_id  As ParentId 
        FROM siebelextract..account
        WHERE row_id = @ChildId
        UNION ALL
        SELECT 
            Rn + 1
            ,a.row_id as Parents
            ,a.parent_account_id As ParentId    
        FROM siebelextract..account a
        JOIN GetParentOfChild gp on a.row_id = gp.ParentId
    )

SELECT TOP 1 @ChildId = Parents 
FROM GetParentOfChild
ORDER BY Rn DESC

What it does is that given any child , it will return the root level parent....The program is perfectly working fine all the time...

Just out of curiosity/experimental sake i changed the JOIN to Left Outer Join and it reported

Msg 462, Level 16, State 1, Procedure GetParent, Line 9 Outer join is not allowed in the recursive part of a recursive common table expression 'GetParentOfChild'.

My question is why recursive part of CTE cannot accept Left Outer Join? Is it by design?

Thanks

解决方案

Yes, it is by design, read Guidelines for Defining and Using Recursive Common Table Expressions

The following items are not allowed in the CTE_query_definition of a recursive member:

• SELECT DISTINCT
• GROUP BY
• HAVING
• Scalar aggregation
• TOP
• LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
• Subqueries

Notice that if your query make a left join to it self through CTE can become to an infinite recursion.

这篇关于为什么我们不能在递归CTE中使用外部联接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！