详解sql递归

递归是指程序调用自身的一种编程技巧,在SQL中也有递归查询,下面为大家详细讲解一下SQL递归。

详解sql递归

递归查询原理

SQL Server中的递归查询是通过CTE(表表达式)来实现。至少包含两个查询,第一个查询为定点成员,定点成员只是一个返回有效表的查询,用于递归的基础或定位点;第二个查询被称为递归成员,使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。

递归查询的终止条件

递归查询没有显式的递归终止条件,只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。是指递归次数上限的方法是使用MAXRECURION。

递归查询的优点

效率高,大量数据集下,速度比程序的查询快。

递归的常见形式

WITH CTE AS (

SELECT column1,column2… FROM tablename WHERE conditions

UNION ALL

SELECT column1,column2… FROM tablename

INNER JOIN CTE ON conditions

)

递归查询示例

创建测试数据,有一个员工表Employee,ManagerID是UserID的父节点,这是一个非常简单的层次结构模型。

USE SQL_Road
GO  
CREATE  TABLE Employee
(
   UserID INT,
   ManagerID INT,
   Name NVARCHAR(10)
)
INSERT  INTO dbo.Employee
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'

查询一下Employee表里的数据

SQL中的递归用法SQL中的递归用法

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

WITH CTE AS(
SELECT UserID,ManagerID,Name,Name AS ManagerName
FROM dbo.Employee
WHERE ManagerID=-1
UNION ALL
SELECT c.UserID,c.ManagerID,c.Name,p.Name AS ManagerName
FROM CTE P
INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID
)

SELECT UserID,ManagerID,Name,ManagerName
FROM CTE

结果如下:

SQL中的递归用法SQL中的递归用法

我们来解读一下上面的代码

查询ManagerID=-1,作为根节点,这是递归查询的起始点。

迭代公式是 UNION ALL 下面的查询语句。在查询语句中调用中CTE,而查询语句就是CTE的组成部分,即 “自己调用自己”,这就是递归的真谛所在。所谓迭代,是指每一次递归都要调用上一次查询的结果集,UNION ALL是指每次都把结果集并在一起。

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

查询路径

下面我们通过层次结构查询子节点到父节点的PATH,我们对上面的代码稍作修改:

WITH CTE AS(
SELECT UserID,ManagerID,Name,CAST(Name AS NVARCHAR(MAX)) AS LPath  
FROM dbo.Employee
WHERE ManagerID=-1
UNION ALL
SELECT c.UserID,c.ManagerID,c.Name,p.LPath+'->'+c.Name AS LPath
FROM CTE P
INNER JOIN dbo.Employee c ON p.UserID=c.ManagerID
)

SELECT UserID,ManagerID,Name,LPath
FROM CTE

其中CAST(Name AS NVARCHAR(MAX))是将Name的长度设置为最大,防止字段过长超出字段长度。具体结果如下:

SQL中的递归用法SQL中的递归用法
SQL中的递归用法SQL中的递归用法

以上就是递归查询的一些知识介绍了,自己可以动手实验一下,这个一般在面试中也经常会考察面试者,希望能帮助到大家~

以上就是良许教程网为各位朋友分享的Linu系统相关内容。想要了解更多Linux相关知识记得关注公众号“良许Linux”,或扫描下方二维码进行关注,更多干货等着你 !

137e00002230ad9f26e78-265x300
本文由 良许Linux教程网 发布,可自由转载、引用,但需署名作者且注明文章出处。如转载至微信公众号,请在文末添加作者公众号二维码。
良许

作者: 良许

良许,世界500强企业Linux开发工程师,公众号【良许Linux】的作者,全网拥有超30W粉丝。个人标签:创业者,CSDN学院讲师,副业达人,流量玩家,摄影爱好者。
上一篇
下一篇

发表评论

邮箱地址不会被公开。 必填项已用*标注

联系我们

联系我们

公众号:良许Linux

在线咨询: QQ交谈

邮箱: yychuyu@163.com

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

关注微博
返回顶部