为什么递归 CTE 无法使用分组和其他子句? [英] Why are recursive CTEs unable to use grouping and other clauses?

问题描述

我最近在寻找一种方法来构建某些数据的特定视图时了解了递归公用表表达式 (CTE).在花了一段时间写出我的查询的第一次迭代将如何工作后，我将其转换为 CTE 以观看整个过程.我很惊讶地看到分组不起作用，所以我只是用选择 TOP 1，ORDER BY"等价物替换了它.我再次惊讶于不允许使用TOP"，并且发现所有这些子句都不允许出现在 CTE 的递归部分中:

• 与众不同
• 分组依据
• 拥有
• 顶部
• 左
• 正确
• 外连接

所以我想我有两个问题:

1. 为了更好地了解我的情况和 SQL，为什么不允许使用这些子句?
2. 如果我需要使用其中的一些子句进行某种递归，那么编写递归存储过程是我唯一的选择吗?

谢谢.

解决方案

参考:-

<块引用>

1. 为了更好地了解我的情况和 SQL，为什么不允许使用这些子句?

根据我对 CTE 的理解，创建 CTE 背后的整个想法是这样您就可以创建一个临时结果集并以命名方式使用它，就像 中的常规表一样SELECT、INSERT、UPDATE 或 DELETE 语句.

因为 CTE 在逻辑上非常像视图，所以 CTE 查询中的 SELECT 语句必须遵循与创建视图相同的要求.请参阅 MSDN 上的以下链接中的**CTE 查询定义* 部分>

另外，因为 CTE 基本上是一个命名的结果集，它可以像 SELECT、INSERT、UPDATE 或DELETE 语句，当您在任何这些语句中使用 CTE 时，您始终可以选择使用您提到的各种运算符.

关于

<块引用>

2.如果我需要使用其中的一些子句进行某种递归，那么我唯一的选择是编写递归存储过程吗?

此外，我很确定您至少可以在视图/CTE 选择语句中使用上面提到的一些关键字.例如:参考在 CTE GROUP BY>这里在创建一个简单的公用表表达式示例

也许，如果您可以提供您尝试实现的目标的示例场景，我们可以建议一些可能的解决方案.

I recently learned about Recursive Common Table Expressions (CTEs) while looking for a way to build a certain view of some data. After taking a while to write out how the first iteration of my query would work, I turned it into a CTE to watch the whole thing play out. I was surprised to see that grouping didn't work, so I just replaced it with a "Select TOP 1, ORDER BY" equivalent. I was again surprised that "TOP" wasn't allowed, and came to find that all of these clauses aren't allowed in the recursive part of a CTE:

• DISTINCT
• GROUP BY
• HAVING
• TOP
• LEFT
• RIGHT
• OUTER JOIN

So I suppose I have 2 questions:

1. In order to better understand my situation and SQL, why aren't these clauses allowed?
2. If I need to do some sort of recursion using some of these clauses, is my only alternative to write a recursive stored procedure?

Thanks.

解决方案

Referring to :-

1. In order to better understand my situation and SQL, why aren't these clauses allowed?

Based on my understanding of CTE's, the whole idea behind creating a CTE is so that you can create a temporary result-set and use it in a named manner like a regular table in SELECT, INSERT, UPDATE, or DELETE statements.

Because a CTE is logically very much like a view, the SELECT statement in your CTE query must follow the same requirements as those used for creating a view. Refer **CTE query definitions* section in following link on MSDN

Also, because a CTE is basically a named resultset and it can be used like any table in SELECT, INSERT, UPDATE, or DELETE statements, you always have the option of using the various operators you mentioned when you use the CTE in any of those statements.

With regarding to

2.If I need to do some sort of recursion using some of these clauses, is my only alternative to write a recursive stored procedure?

Also, I am pretty sure that you can use at least some of the keywords that you have mentioned above in a view / CTE select statement. For Example: Refer to the use of GROUP BY in a CTE here in the Creating a simple common table expression example

Maybe, if you can provide a sample scenario of what you are trying to achieve, we can suggest some possible solution.

这篇关于为什么递归 CTE 无法使用分组和其他子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！