为什么递归 CTE 无法使用分组和其他子句?

发布于 2024-09-26 21:10:12 字数 455 浏览 3 评论 0原文

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

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

所以我想我有 2 个问题:

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

谢谢。

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

鲜血染红嫁衣 2024-10-03 21:10:12

参考:-

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

根据我对 CTE 的理解,创建 CTE 背后的整个想法是,您可以创建一个临时结果集并以命名方式使用它,就像SELECT、INSERTUPDATEDELETE 语句。

由于 CTE 在逻辑上非常类似于视图,因此 CTE 查询中的 SELECT 语句必须遵循与创建视图所用的相同要求。请参阅 MSDN 上以下链接中的 **CTE 查询定义* 部分

另外,因为 CTE 基本上是一个命名结果集,它可以像 SELECTINSERTUPDATEDELETE< 中的任何表一样使用/code> 语句,当您在任何这些语句中使用 CTE 时,您始终可以选择使用您提到的各种运算符。

与关于

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

另外,我非常确定您至少可以在 view / CTE select statements 中使用上面提到的一些关键字。例如:请参阅 CTE 中 GROUP BY 的使用 在创建一个简单的公用表表达式示例中

也许,如果您可以提供您想要实现的目标的示例场景,我们可以建议一些可能的解决方案。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文