首页 / MSSQL / SQL Server CTE 递归查询全解

SQL Server CTE 递归查询全解

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server CTE 递归查询全解，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5579字，纯文字阅读大概需要8分钟。

内容图文

一，递归查询原理

CTE的递归查询必须满足三个条件：初始条件，递归调用表达式，终止条件，CTE 递归查询的伪代码如下：

WITH cte_name ( column_name [,...n] )
AS
(
--Anchor member is defined
CTE_query_definition 
UNION ALL
--Recursive member is defined referencing cte_name
CTE_query_definition 
)
-- Statement using the CTE
SELECT *
FROM cte_name

1，递归查询至少包含两个子查询：

第一个子查询称作定点（Anchor）子查询：定点查询只是一个返回有效表的查询，用于设置递归的初始值；
第二个子查询称作递归子查询：该子查询调用CTE名称，触发递归查询，实际上是递归子查询调用递归子查询；
两个子查询使用union all，求并集；

2，CTE的递归终止条件

递归查询没有显式的递归终止条件，只有当递归子查询返回空结果集（没有数据行返回）或是超出了递归次数的最大限制时，才停止递归。

默认的递归查询次数是100，可以使用查询提示（hint）：MAXRECURSION 控制递归的最大次数：OPTION( MAXRECURSION 16)；如果允许无限制的递归次数，使用查询提示：option(maxrecursion 0)；当递归查询达到指定或默认的 MAXRECURSION 数量限制时，SQL Server将结束查询并返回错误，如下：

The statement terminated. The maximum recursion 10 has been exhausted before statement completion.

事务执行失败，该事务包含的所有操作都被回滚。在产品环境中，慎用maxrecursion 查询提示，推荐通过 where 条件限制递归的次数。

3，递归步骤

step1：定点子查询设置CTE的初始值，即CTE的初始值Set0；

递归调用的子查询过程：递归子查询调用递归子查询；

step2：递归子查询第一次调用CTE名称，CTE名称是指CTE的初始值Set0，第一次执行递归子查询之后，CTE名称是指结果集Set1；

step3：递归子查询第二次调用CTE名称，CTE名称是指Set1，第二次执行递归子查询之后，CTE名称是指结果集Set2；

step4：在第N次执行递归子查询时，CTE名称是指Set(N-1)，递归子查询都引用前一个递归子查询的结果集；

Step5：如果递归子查询返回空数据行，或超出递归次数的最大限制，停止递归；

二，递归查询示例（员工职称）

1，创建测试数据

ManagerID是UserID的父节点，这是一个非常简单的层次结构模型。

use tempdb
go 

create table dbo.dt_user
(
    UserID int,
    ManagerID int,
    Name Nvarchar(10)
)

insert into dbo.dt_user
select 1,-1,N‘Boss‘
union all
select 11,1,N‘A1‘
union all
select 12,1,N‘A2‘
union all
select 13,1,N‘A3‘
union all
select 111,11,N‘B1‘
union all
select 112,11,N‘B2‘
union all
select 121,12,N‘C1‘

2，查询每个User的的直接上级Manager

;with cte as
(
select UserID,ManagerID,name,name as ManagerName
from dbo.dt_user
where ManagerID=-1

union all
select c.UserID,c.ManagerID,c.Name,p.name as ManagerName
from cte P
inner join dbo.dt_user c
    on p.UserID=c.ManagerID
)
select UserID,ManagerID,Name,ManagerName
from cte
order by UserID

step1：查询ManagerID=-1，作为root node，这是递归查询的起始点。

step2：迭代公式是 union all 下面的查询语句。在查询语句中调用中cte，而查询语句就是cte的组成部分，即 “自己调用自己”，这就是递归的真谛所在。

所谓迭代，是指每一次递归都要调用上一次查询的结果集，Union ALL是指每次都把结果集并在一起。

step3-N，迭代公式利用上一次查询返回的结果集执行特定的查询，直到CTE返回null 或达到最大的迭代次数，默认值是32。最终的结果集是迭代公式返回的各个结果集的并集，求并集是由Union All 子句定义的，并且只能使用Union ALL。

SQL Server CTE 递归查询全解 - 文章图片

3，查询路径，在层次结构中查询子节点到父节点的path

;with cte as
(
select UserID,ManagerID,name,cast(name as nvarchar(max)) as ReportPath
from dbo.dt_user
where ManagerID=-1

union all
select c.UserID,c.ManagerID,c.Name,c.name+‘->‘+p.ReportPath as ReportPath
from cte P
inner join dbo.dt_user c
    on p.UserID=c.ManagerID
)
select UserID,ManagerID,Name,ReportPath
from cte
order by UserID

查询结果如下截图：

SQL Server CTE 递归查询全解 - 文章图片

三，递归查询示例（行政区划）

1，需求模拟

在TSQL中实现层次结构，例如有这样一种数据结构，省，市，县，乡，村，如何使用一张表表示这种数据结构，并且允许是不对称的，例如，上海市是个直辖市，没有省份。

create table dbo.hierarchy
(
ID  int not null primary key,
--type int not null,
ParentID int not null,
name varchar(100) not null
)

type表示类型，可以设置：省，Type是1；市，type是2，以此类推。

ParentID标识的是父级ID，例如信阳市的ParentID是河南省的ID。

2，插入测试数据

测试数据格式说明了归属关系，博主懒，去掉type字段。

insert into dbo.hierarchy
values(1,0,‘河南省‘)
,(2,1,‘信阳市‘),(3,2,‘淮滨县‘),(4,3,‘芦集乡‘),(12,3,‘邓湾乡‘),(13,3,‘台头乡‘),(14,3,‘谷堆乡‘)
              ,(8,2,‘固始县‘),(9,8,‘李店乡‘)
              ,(10,2,‘息县‘),(11,10,‘关店乡‘)
,(5,1,‘安阳市‘),(6,5,‘滑县‘),(7,6,‘老庙乡‘)
,(15,1,‘南阳市‘),(16,15,‘方城县‘)
,(17,1,‘驻马店市‘),(18,17,‘正阳县‘)


select * 
from dbo.hierarchy 
order by ParentID

3，实现由父级向子级的查询

由于实际的数据可能有很多，所以，要想获取河南省下的所有市，县，乡，村等信息，必须使用递归查询

;with cte(Id,ParentID,Name) as
(
select * 
from dbo.hierarchy 
where id=1

union all
select h.* 
from dbo.hierarchy h
inner join cte c on h.ParentID=c.id 
--where c.id!=h.ID
)
select *
from cte
order by ParentID

如果要查看向内递归到多少level，可以使用派生列，level=0是省level，level=1是市level，依次类推。

;with cte(Id,ParentID,Name,Level) as
(
select ID,ParentID,Name,0 as Level
from dbo.hierarchy 
where id=1

union all
select h.ID,h.ParentID,h.Name,c.Level+1 as Level
from dbo.hierarchy h
inner join cte c on h.ParentID=c.id 
--where c.id!=h.ID
)
select *
from cte
order by ParentID

查询结果如图：

SQL Server CTE 递归查询全解 - 文章图片

4，由子级向父级的递归查询

;with cte as
(
select ID,ParentID,name
from dbo.hierarchy
where id=4 --芦集乡的ID

union all
select h.ID,h.ParentID,h.name
from dbo.hierarchy h
inner join cte c on h.id=c.ParentID
)
select ID,ParentID,name
from cte
order by ParentID

查询结果如图：

SQL Server CTE 递归查询全解 - 文章图片

http://blog.csdn.net/3150379/article/details/54865603

SQL Server CTE 递归查询全解

标签：set title where 一个简单的省份脚本 union imu

本文系统来源：https://www.cnblogs.com/LCX/p/8367472.html

内容总结

以上是互联网集市为您收集整理的SQL Server CTE 递归查询全解全部内容，希望文章能够帮你解决SQL Server CTE 递归查询全解所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/498143.html

来源：【匿名】

【上一篇】SQL Server表的连接类型【下一篇】一个odbc连mssql分页的类

更多 ►

【SQL Server CTE 递归查询全解】教程文章相关的互联网学习教程文章

mssql 递归【图】

--递归id往下所有内容with temp ( URE_ID, URE_PARENT_ID,URE_NAME ,URE_TYPE)as(select URE_ID, URE_PARENT_ID,URE_NAME ,URE_TYPEfrom ASM_USER_REGIONwhere URE_PARENT_ID= 23union allselect a.URE_ID, a.URE_PARENT_ID,a.URE_NAME ,a.URE_TYPEfrom ASM_USER_REGION ainner join temp on a.URE_PARENT_ID = temp.URE_ID)select * from temp --递归id往上所有内容with temp ( URE_ID, URE_PARENT_ID,URE_NAME ,URE_TYPE)as(sel...

在sql server中利用with as实现递归功能

在sqlserver2005之前，要实现递归功能比较麻烦，比如可能会要用到临时表与while语句来循环。自sqlserver2005之后，新增了with as功能语法，即公用表达式(CTE),让递归实现起来变的简单了。本章我们主要演示如何利用with as功能实现一个简单的递归功能。在这之前先看一下cte的语法：[ WITH <common_table_expression > [ ,...n ] ]<common_table_expression >::= expression_name [ ( column_name [ ,...n ] ) ] AS ...

SQLServer 递归查询

--SQLServer 递归查询，主要用于建立树形结构IF OBJECT_ID('Categories') IS NOT NULLDROP TABLE Categories GOCREATE TABLE Categories (CategoryID INT, CategoryName VARCHAR(20),ParentID INT ) GOINSERT INTO Categories(CategoryID, CategoryName, ParentID)SELECT 1, 'Books', NULL UNION ALLSELECT 2, 'SQL Server', 1 UNION ALLSELECT 3, 'ASP.NET', 1 UNION ALLSELECT 4, 'General', 2 UNION ALLSELECT 5, 'SSIS', 2 UNI...

SQLServer 递归查询【代码】

--SQLServer 递归查询，主要用于建立树形结构IF OBJECT_ID(Categories) IS NOT NULLDROP TABLE Categories GOCREATE TABLE Categories (CategoryID INT, CategoryName VARCHAR(20),ParentID INT ) GOINSERT INTO Categories(CategoryID, CategoryName, ParentID)SELECT 1, Books, NULL UNION ALLSELECT 2, SQL Server, 1 UNION ALLSELECT 3, ASP.NET, 1 UNION ALLSELECT 4, General, 2 UNION ALLSELECT 5, SSIS, 2 UNION ALLSELECT...

sqlserver with as 双向递归【代码】【图】

递归上下级部门，获取相关的部门单位部门表： sql语句：with Temp1 as ( select GroupID,G_ParentID from sys_Group where GroupID={当前部门GroupID} union all select sys_Group.GroupID,sys_Group.G_ParentID from Temp1 inner join sys_Group on Temp1.G_ParentID=sys_Group.GroupID ), Temp2 as( select GroupID,G_ParentID from sys_Group where GroupID={当前部门GroupID} union all select sys_Group.GroupID,sys_Group...

SQL Server使用递归查询数据

SQL Server使用递归查询数据标签：本文系统来源：http://www.cnblogs.com/yourchoose/p/4531221.html

SqlServer CTE 递归查询 Oracle递归查询

http://msdn.microsoft.com/zh-cn/library/ms186243(v=SQL.90).aspx。 MsSql的语法如下：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 递归执行的语义如下：将 CTE 表达式拆分为定位点成员和递归成员。运行定位点...

SqlServer_合并多个递归查询数据（CTE）

--cteParents 父亲信息 with cteChildren(EmpID,ParentID) AS ( select EmpID,ParentID from Persons where parentid = 10171 union all select t.EmpID,t.parentid from Persons as t inner join cteChildren as c on t.ParentID = c.Id ), cteParents(EmpID,ParentID) AS ( select EmpID,ParentID from Persons where Id= 10172 union all select t.EmpID,t.parentid from Persons as t inner join cteParents as c ...

使用cursor递归遍历sqlserver的相应表

rc GODECLARE @nnn varchar(100)DECLARE cur1 cursor for select [name] from sys.tables where name LIKE ‘index_%‘drop table #Tmp --删除临时表#Tmp create table #Tmp --创建临时表#Tmp (kkk uniqueidentifier );INSERT #Tmp VALUES(‘595C4D7D-B9E9-4E29-85F5-05C414CA961E‘) INSERT #Tmp VALUES(‘92DFF3AF-7B65-43EF-AA64-069AF84896AA‘) INSERT #Tmp VALUES(‘A3638B49-4CF0-4046-8349-0AB12560CDE6‘) INSERT #Tmp...

sqlserver向上递归统计【代码】

select a.kmcode,a.kmname,a.pidkm xx sys_km a where kmcode in ( select t1.yskm from view_dj t1 where t1.swdjh=@bm)union all select k.kmcode,k.kmname,k.pidkm xx sys_km k inner join cte c on c.pidkm = k.kmcode ) 3.在通过cte和金额表关联把数据插入到临时表中代码如下 insert into #tmphjcx select * from ( select distinct a.kmcode,a.kmname,a.pidkm, b.hjje,b.guoshui,b.dishui from cte aleft j...

SqlServer 三级联动、递归表

SqlServer 省市县三级联动三张表递归合并成一张表sql如下： insert into table2(area_name,area_parent_id)select province,‘0‘ from provincesinsert into table2(area_name,area_parent_id)select city,c.id from cities ainner join provinces b on a.provinceid=b.provinceidinner join table2 c on b.province=c.area_nameinsert into table2(area_name,area_parent_id)select a.area,c.id from areas ainner join ...

Sql Server递归查询（转）

drop table tb; create table tb(id varchar(3) , pid varchar(3) , name varchar(10)); insert into tb values(‘001‘ , null , ‘广东省‘); insert into tb values(‘002‘ , ‘001‘ , ‘广州市‘); insert into tb values(‘003‘ , ‘001‘ , ‘深圳市‘) ; insert into tb values(‘004‘ , ‘002‘ , ‘天河区‘) ; insert into tb values(‘005‘ , ‘003‘ , ‘罗湖区‘); insert into tb values(...

Sql Server CTE递归【图】

WITH cte AS ( SELECT a.FNUMBER,a.FMATERIALID AS MainId,b.FMATERIALID AS ChileID,CAST(b.FMATERIALID AS VARCHAR(max)) AS lev FROM t_eng_bom a JOIN dbo.T_ENG_BOMCHILD b ON a.fid =b.FID WHERE a.fid = ‘100150‘ UNION ALL SELECT d.FNUMBER,d.MainId,d.ChileID,CAST(lev AS VARCHAR(max))+ CAST(d.ChileID AS VARCHAR(max)) AS lev FROM cte c JOIN ( SELECT aa.FNUMBER,aa.FMATERIALID AS MainId,bbb.FMATERIALID ...

SQL Server 树形表非循环递归查询【图】

很多人可能想要查询整个树形表关联的内容都会通过循环递归来查...事实上在微软在SQL2005或以上版本就能用别的语法进行查询，下面是示例。 --通过子节点查询父节点WITH TREE AS( SELECT * FROM Areas WHERE id = 6 -- 要查询的子 id UNION ALL SELECT Areas.* FROM Areas, TREE WHERE TREE.PId = Areas.Id ) SELECT Area FROM TREE --通过父节点查询子节点WITH TREE AS( SELECT * FROM Areas ...

SQL Server中公用表表达式（CTE）递归的生成帮助数据【代码】【图】

GenerateHelpData as (select 0 as idunion allselect id+1 from GenerateHelpData where id<2047 ) select id from GenerateHelpData option (maxrecursion 2047);可以直接让CTE参数逻辑运算，也可以生成临时表，达到多次重用的目的，这样感觉是不是也很清爽？ 1，生成连续数字（当然数字的起始值，间隔值都可以自定义）--生成连续数字 ;with GenerateHelpData as (select 0 as idunion allselect id+1 from GenerateHelpData w...

递归查询 - 相关标签

递归查询

MSSQL - 最热教程

sqlServer 查询特定的表（字段或者文字...sqlserver建表及注释 sql server 2008出现远程过程调用失败 Window Server2019安装SqlServer2012管...SQLserver使用truncate语句报错：找不...sql server导出到excel错误：未在本地计...SQL Server 2008 下载及安装教程 SQL SERVER将多行数据合并成一行（转）SQL SERVER 中设置字段默认值为当前时间 php Sql Server连接失败问题及解决办法

首页 / MSSQL / SQL Server CTE 递归查询全解

SQL Server CTE 递归查询全解

内容导读

内容图文

内容总结

内容备注

内容手机端

【SQL Server CTE 递归查询全解】教程文章相关的互联网学习教程文章

mssql 递归【图】

在sql server中利用with as实现递归功能

SQLServer 递归查询

SQLServer 递归查询【代码】

sqlserver with as 双向递归【代码】【图】

SQL Server使用递归查询数据

SqlServer CTE 递归查询 Oracle递归查询

SqlServer_合并多个递归查询数据（CTE）

使用cursor递归遍历sqlserver的相应表

sqlserver向上递归统计【代码】

SqlServer 三级联动、递归表

Sql Server递归查询（转）

Sql Server CTE递归【图】

SQL Server 树形表非循环递归查询【图】

SQL Server中公用表表达式（CTE）递归的生成帮助数据【代码】【图】

SERVER - 相关标签

递归查询 - 相关标签

SQL - 相关标签

MSSQL - 最新教程

MSSQL - 最热教程