为什么递归 CTE 无法使用分组和其他子句?
最近,我在寻找一种方法来构建某些数据的特定视图时了解了递归公用表表达式 (CTE)。在花了一些时间写出查询的第一次迭代如何工作后,我将其转换为 CTE 来观看整个过程。我很惊讶地发现分组不起作用,所以我只是将其替换为“Select TOP 1, ORDER BY”等效项。我再次惊讶于不允许使用“TOP”,并发现所有这些子句都不允许出现在 CTE 的递归部分中:
- DISTINCT
- GROUP BY
- HAVING
- TOP
- LEFT
- RIGHT
- OUTER JOIN
所以我想我有 2 个问题:
- 为了更好地理解我的情况和SQL,为什么不允许使用这些子句?
- 如果我需要使用其中一些子句进行某种递归,那么编写递归存储过程是我唯一的选择吗?
谢谢。
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:
- In order to better understand my situation and SQL, why aren't these clauses allowed?
- 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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
参考:-
根据我对 CTE 的理解,创建 CTE 背后的整个想法是,您可以创建一个临时结果集并以命名方式使用它,就像
SELECT、
INSERT
、UPDATE
或DELETE
语句。由于 CTE 在逻辑上非常类似于视图,因此 CTE 查询中的 SELECT 语句必须遵循与创建视图所用的相同要求。请参阅 MSDN 上以下链接中的 **CTE 查询定义* 部分
另外,因为 CTE 基本上是一个命名结果集,它可以像
SELECT
、INSERT
、UPDATE
或DELETE< 中的任何表一样使用/code> 语句,当您在任何这些语句中使用 CTE 时,您始终可以选择使用您提到的各种运算符。
与关于
另外,我非常确定您至少可以在 view / CTE select statements 中使用上面提到的一些关键字。例如:请参阅 CTE 中
GROUP BY
的使用 在创建一个简单的公用表表达式示例中也许,如果您可以提供您想要实现的目标的示例场景,我们可以建议一些可能的解决方案。
Referring to :-
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
, orDELETE
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 MSDNAlso, because a CTE is basically a named resultset and it can be used like any table in
SELECT
,INSERT
,UPDATE
, orDELETE
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
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 exampleMaybe, if you can provide a sample scenario of what you are trying to achieve, we can suggest some possible solution.