SQL高级知识:递归查询

递归查询原理

SQL中的递归查询是通过CTE(表表达式)来实现。至少包含两个查询:

  • 第一个查询为定点成员,定点成员只是一个返回有效表的查询,用于递归的基础或定位点;
  • 第二个查询被称为递归成员,使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。

递归查询的终止条件

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

递归查询的优点

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

递归的常见形式

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表里的数据

图片

查询每个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

结果如下:

图片

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

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

2、迭代公式是 UNION ALL 下面的查询语句。在查询语句中调用中CTE,而查询语句就是CTE的组成部分,即 “自己调用自己”,这就是递归的真谛所在。

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

3、迭代公式利用上一次查询返回的结果集执行特定的查询,直到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的长度设置为最大,防止字段过长超出字段长度。具体结果如下:

图片

原创文章,作者:guozi,如若转载,请注明出处:https://www.sudun.com/ask/89728.html

(0)
guozi's avatarguozi
上一篇 2024年6月5日 上午10:19
下一篇 2024年6月5日 上午10:22

相关推荐

  • 电商网站开发

    电商网站开发,是当今互联网时代不可或缺的一项技术。它不仅能为企业带来巨大的商业价值,也为消费者提供了更加便捷、快捷的购物体验。但是,要想开发出一款优秀的电商网站,并非易事。它涉及到…

    行业资讯 2024年3月28日
    0
  • 如何快速完成网页制作流程?

    你是否经常为网页制作流程繁琐而感到头痛?想要快速完成网页制作却不知道从何入手?别担心,今天我将为你揭开如何快速完成网页制作流程的秘密。通过本文,你将了解到网页制作流程的重要性,以及…

    行业资讯 2024年3月28日
    0
  • 俄罗斯的vps适合什么网站(VPS是什么?VPS什么用途?)

    随着互联网的发展,虚拟私人服务器(VPS)逐渐成为网站建设者的首选。然而,您是否听说过俄罗斯的VPS?它究竟适合什么样的网站呢?在这篇文章中,我们将带您深入了解俄罗斯VPS市场的概…

    行业资讯 2024年4月1日
    0
  • 如何选择合适的成都网站设计服务商?

    在当今数字化时代,网站已经成为企业发展的重要窗口。而选择一家合适的成都网站设计服务商,则更是至关重要。但是如何确定自己的网站需求?选择合适的成都网站设计服务商又有哪些关键因素?如何…

    行业资讯 2024年4月11日
    0

发表回复

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