关于withas递归问题 sql server with 递归

WITHlocs(id,name,parent,loclevel,cc)//列名,查询的列数要一置.查询结果表显示的列表AS(SELECTtype_id,type_name,type_father_id,type_layer as loclevel,0 as ccFROM tb_typeWHEREtype_father_id='1'UNIONALLSELECTl.type_id,l.type_name,l.type_father_id,type_layer as loclevel,cc+1FROM tb_type lINNERJOIN locs p ON l.type_father_id=p.id)
关于withas递归问题 sql server with 递归
SELECT* FROM locs order by loclevel

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

SQL Server 2008 R2其他版本

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

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

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

递归CTE 的结构

Transact-SQL 中的递归 CTE的结构与其他编程语言中的递归例程相似。尽管其他语言中的递归例程返回标量值,但递归 CTE 可以返回多行。

递归 CTE 由下列三个元素组成:

  1. 例程的调用。

    递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT运算符联接的CTE_query_definitions。由于这些查询定义形成了CTE 结构的基准结果集,所以它们被称为“定位点成员”。

    CTE_query_definitions被视为定位点成员,除非它们引用了CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前,而且必须使用 UNION ALL运算符联接最后一个定位点成员和第一个递归成员。

  2. 例程的递归调用。

    递归调用包括一个或多个由引用 CTE 本身的 UNION ALL运算符联接的CTE_query_definitions。这些查询定义被称为“递归成员”。

  3. 终止检查。

    终止检查是隐式的;当上一个调用中未返回行时,递归将停止。

注意

如果递归 CTE组合不正确,可能会导致无限循环。例如,如果递归成员查询定义对父列和子列返回相同的值,则会造成无限循环。在测试递归查询的结果时,可以通过在INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到32,767 之间的值,来限制特定语句允许的递归级数。有关详细信息,请参阅查询提示(Transact-SQL)和WITHcommon_table_expression_r(Transact-SQL)。

伪代码和语义

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

WITHcte_name ( column_name [,...n] )

AS

(

CTE_query_definition–- Anchor member is defined.

UNIONALL

CTE_query_definition–- Recursive member is defined referencing cte_name.

)

--Statement using the CTE

SELECT*

FROMcte_name

递归执行的语义如下:

  1. 将 CTE 表达式拆分为定位点成员和递归成员。

  2. 运行定位点成员,创建第一个调用或基准结果集 (T0)。

  3. 运行递归成员,将 Ti作为输入,将Ti+1作为输出。

  4. 重复步骤 3,直到返回空集。

  5. 返回结果集。这是对 T0到Tn执行UNIONALL 的结果。

示例

以下示例通过返回 Adventure WorksCycles 公司的雇员的分层列表(从最高级雇员开始)显示递归 CTE 结构的语义。示例后面是代码执行的演练。

-- Create an Employee table. CREATE TABLE dbo.MyEmployees ( EmployeeID smallint NOT NULL, FirstName nvarchar(30) NOT NULL, LastName nvarchar(40) NOT NULL, Title nvarchar(50) NOT NULL, DeptID smallint NOT NULL, ManagerID int NULL, CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) ); -- Populate the table with values. INSERT INTO dbo.MyEmployees VALUES (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL) ,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1) ,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273) ,(275, N'Michael', N'Blythe', N'Sales Representative',3,274) ,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274) ,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273) ,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285) ,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273) ,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
USE AdventureWorks2008R2; GO WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level) AS ( -- Anchor member definition SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 0 AS Level FROM dbo.MyEmployees AS e INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL WHERE ManagerID IS NULL UNION ALL -- Recursive member definition SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, Level + 1 FROM dbo.MyEmployees AS e INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) -- Statement that executes the CTE SELECT ManagerID, EmployeeID, Title, DeptID, Level FROM DirectReports INNER JOIN HumanResources.Department AS dp ON DirectReports.DeptID = dp.DepartmentID WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0; GO

示例代码演练

  1. 递归 CTEDirectReports定义了一个定位点成员和一个递归成员。

  2. 定位点成员返回基准结果集 T0。这就是公司中的最高级雇员,即不向经理报告的雇员。

    以下是定位点成员返回的结果集:

    ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ NULL1Chief Executive Officer0
  3. 递归成员返回定位点成员结果集中的雇员的直接下属。这是通过在Employee表和DirectReportsCTE之间执行联接操作获得的。正是此次对 CTE 自身的引用建立了递归调用。利用CTEDirectReports中的雇员作为输入(Ti),联接(MyEmployees.ManagerID= DirectReports.EmployeeID) 返回经理为 (Ti)的雇员作为输出 (Ti+1)。这样,递归成员的第一次迭代返回了以下结果集:

    ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ 1273Vice President of Sales1
  4. 重复激活递归成员。递归成员的第二次迭代使用步骤 3中的单行结果集(包含EmployeeID273)作为输入值,并返回以下结果集:

    ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ 27316Marketing Manager2 273274North American Sales Manager2 273285Pacific Sales Manager2

    递归成员的第三次迭代使用上面的结果集作为输入值,并返回以下结果集:

    ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ 1623Marketing Specialist3 274275Sales Representative3 274276Sales Representative3 285286Sales Representative3
  5. 正在运行的查询返回的最终结果集是定位点成员和递归成员生成的所有结果集的并集。

    以下是示例返回的完整结果集:

    ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ NULL1Chief Executive Officer0 1273Vice President of Sales1 27316Marketing Manager2 273274North American Sales Manager2 273285Pacific Sales Manager2 1623Marketing Specialist3 274275Sales Representative3 274276Sales Representative3 285286Sales Representative3

  

爱华网本文地址 » http://www.413yy.cn/a/25101014/190564.html

更多阅读

如何配置SQL Server 2008管理器 sqlserver2008配置

如何配置SQL Server 2008管理器——简介SQl Server 配置管理器(简称为配置管理器)包含了SQL Server 2008服务、SQL Server 2008网络配置和SQL Native Client配置3个工具,供数据库管理人员做服务器启动停止与监控、服务器端支持的网络协

sql server 2008 r2安装详解 sqlserver2008r2 64位

sql server 2008 r2安装详解——简介本篇文章为大家介绍了安装sql server 2008 安装图解,里面有详细步骤,以及需要注意的事项,希望能帮助有需要的朋友。一、进入安装程序插入SQL Server 2008 R2安装光盘,自动运行后出现“SQL Server

SQL Server SQL触发器经验详解 sqlserver 触发器调试

【SQL Server】SQL触发器经验详解——简介自从上次在经验中使用了触发器,有读者询问我一些关于触发器的相关信息,个人推荐首先你需要先去把触发器最基础的东西了解清楚,然后通过这次的经验,希望能在你理解的基础上,加深你对触发器的了解,

SQL Server如何导入mdf,ldf文件 mdf导入sql server

SQL Server如何导入mdf,ldf文件——简介在平时开发中,经常会遇到数据库SQL Server如导入mdf,ldf文件的问题,下面给你介绍一下怎么做,希望能够帮到你。SQL Server如何导入mdf,ldf文件——工具/原料SQL Servermdf,ldf文件SQL Server如何导

声明:《关于withas递归问题 sql server with 递归》为网友大众男友分享!如侵犯到您的合法权益请联系我们删除