当前位置:Gxlcms > mysql > 使用公用表表达式的递归查询

使用公用表表达式的递归查询

时间:2021-07-01 10:21:17 帮助过:15人阅读

微软从SQL2005起引入了CTE(Common Table Expression)以强化T-SQL。 公用表表达式 (CTE) 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。 当某个查询引用递

  微软从SQL2005起引入了CTE(Common Table Expression)以强化T-SQL。

  公用表表达式 (CTE) 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

  当某个查询引用递归 CTE 时,它即被称为递归查询。递归查询通常用于返回分层数据,例如:显示某个组织图中的雇员或物料清单方案(其中父级产品有一个或多个组件,而那些组件可能还有子组件,或者是其他父级产品的组件)中的数据。

  递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中,递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。有关公用表表达式的详细信息,请参阅使用公用表表达式。

伪代码和语义

————————————————————————————

递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT * FROM cte_name

递归执行的语义如下:

  1. 将 CTE 表达式拆分为定位点成员和递归成员。
  2. 运行定位点成员,创建第一个调用或基准结果集 (T0)。
  3. 运行递归成员,将 Ti 作为输入,将 Ti+1 作为输出。
  4. 重复步骤 3,直到返回空集。
  5. 返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。

    示例

    ————————————————————————————

    原表:

    \

     现在有一个需求,要查询出某个省下面的所有市和区(查询结果包含省)。如果只使用SQL语句来实现,需要使用到游标、临时表等技术。但在SQL Server2005中还可以使用CTE来实现。

    WITH district

    AS

    (

    --获得第一个结果集,并更新最终结果集

    SELECT * FROM t_tree WHERE id = 0

    UNION ALL

    --下面的select语句首先会根据从上一个查询结果集中获得的id值来查询parent_id

    --字段的值,然后district就会变当前的查询结果集,并继续执行下面的select 语句

    --如果结果集不为null,则与最终的查询结果合并,同时用合并的结果更新最终的查

    --询结果;否则停止执行。最后district的结果集就是最终结果集。

    SELECT a.* FROM t_tree aINNER JOIN district bONa.parent_id = b.id

    )

    SELECT * FROM district

    查询结果:

    \

    有关使用公用表表达式的详细信息,请参阅使用公用表表达式的递归查询。

人气教程排行