SQL中的递归CTE [英] Recursive CTE in SQL

问题描述

亲爱的朋友，

解释SQL中递归CTE的用法

问候，
Arunprasath Natarajan

Dear Friends,

Explain the usage of Recursive CTE in SQL

Regards,
Arunprasath Natarajan

推荐答案

请参阅以下内容:

http://blog.sqlauthority.com/2008/07 /28/sql-server-simple-example-of-recursive-cte/ [ http://www.mssqltips.com /sqlservertip/1520/recursive-queries-using-common-table-expressions-cte-in-sql-server/ [

Refer these:

http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/[^]

http://www.mssqltips.com/sqlservertip/1520/recursive-queries-using-common-table-expressions-cte-in-sql-server/[^]

在用SQL设计查询时，通常我们可能需要对系统内部不存在的一组数据进行操作.例如，电子商务Web应用程序的数据库将具有标准表-产品，客户，订单，订单明细等，但我们可能需要针对特定​​数据子集或针对这些表中的汇总数据运行报告.或者，我们需要的报告查询可能需要按标量子查询返回的结果进行分组或过滤.通常，视图用于将复杂的查询分解为可消化的块或提供可以分组和过滤的标量子查询结果.但是，视图有时是过大的，因为它们是系统级的永久对象.如果只需要在单个存储过程或UDF中引用此复杂查询，则另一种选择是使用派生表.不幸的是，派生表混淆了查询的可读性，并且对于语句中的每次使用都必须重复进行.

公用表表达式(CTE)是Microsoft SQL Server 2005中引入的新结构，它提供了一种更具可读性的派生表形式，可以在查询中声明一次并多次引用该表.


请点击以下链接获取CTE文章:
http://www.simple-talk.com/sql/t -sql-programming/sql-server-cte-basics/ [

Hi,
When crafting a query in SQL, there are often times when we may need to operate over a set of data that doesn''t inherently exist within the system. For example, the database for an eCommerce web application would have the standard tables - Products, Customers, Orders, OrderDetails, and so on - but we may need to run reports on a particular subset of the data or against aggregate data across these tables. Or the reporting queries we need might need to group or filter by results returned by scalar subqueries. Typically, views are used to break down complex queries into digestible chunks or to provide scalar subquery results that can be grouped and filtered. Views, however, are sometimes overkill, as they are permanent objects at the system-level. If we only need to reference this complex query in a single stored procedure or UDF, another option is to use a derived table. Unfortunately, derived tables muddle the readability of the query and must be repeated for each use in a statement.

Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query.


please follow the link below for an article of CTE:
http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/[^]

-AK

这篇关于SQL中的递归CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！