T-SQL递归

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
简介:

摘要:在程序中调用递归很简单只要在方法中调用自身就可以了,可是在数据库操作中这种方法并不可行,那么下面就来看一下在SQL Server中如何书写递归调用。

主要内容:

可以说直到SQL Server 2005之前,SQL Server在运行时级别是没有对递归处理的设计的。这也就是说如果你想要实现递归就只能自己控制递归逻辑,将递归转换为循环操作。但是到了SQL Server2005之后,递归的书写方式应该说就简单的多了,因为SQL Server 2005引入了CTE(Common Table Expression 公用表表达式)。下面是msdn中关于CTE的简单说明:

可以将公用表表达式 (CTE) 视为临时结果集,在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内进行定义。CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次。

CET 可用于:

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

在不需要常规使用视图时替换视图,也就是说,不必将定义存储在元数据中。

启用按从标量嵌套 select 语句派生的列进行分组,或者按不确定性函数或有外部访问的函数进行分组。

在同一语句中多次引用生成的表。

使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后,这些简单块可用于生成更复杂的临时 CTE,直到生成最终结果集。

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

例程的调用。

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

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

例程的递归调用。

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

终止检查。

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

有了CTE之后在SQL Server中使用递归就简单的多了,因为你可以完全不用考虑递归调用的结束条件了,真个递归的调用流程交给了数据库本身来处理。下面直接来看一下递归的使用,首先使用下面的语句来构建一个递归的使用环境:

复制代码
--Create Table
IF EXISTS (SELECT [name] FROM dbo.sysobjects WHERE [name]='Tree' AND type='u' )
BEGIN
IF EXISTS(SELECT [name] FROM dbo.sysobjects WHERE [name]='Info' AND type='u')
DROP TABLE Info
DROP TABLE Tree
END
ELSE
BEGIN
CREATE TABLE Tree
(
id BIGINT PRIMARY KEY,
[name] NVARCHAR(50) NOT NULL,
parentID BIGINT FOREIGN KEY REFERENCES Tree(id) ON DELETE NO ACTION NOT NULL
)
END
IF EXISTS(SELECT [name] FROM dbo.sysobjects WHERE [name]='Info' AND type='u')
DROP TABLE Info
ELSE
BEGIN
CREATE TABLE Info
(
id BIGINT PRIMARY KEY FOREIGN KEY REFERENCES Tree(id) ON DELETE CASCADE,
info NVARCHAR(500)
)
END
-- Insert Data
DELETE FROM dbo.Tree
DELETE FROM dbo.Info
INSERT INTO dbo.Tree VALUES(1,'A',0)
INSERT INTO dbo.Tree VALUES(2,'B',1)
INSERT INTO dbo.Tree VALUES(3,'C',1)
INSERT INTO dbo.Tree VALUES(4,'D',2)
INSERT INTO dbo.Tree VALUES(5,'E',2)
INSERT INTO dbo.Tree VALUES(6,'F',3)
INSERT INTO dbo.Tree VALUES(7,'G',3)
INSERT INTO dbo.Tree VALUES(8,'H',4)
INSERT INTO dbo.Tree VALUES(9,'I',4)
INSERT INTO dbo.Tree VALUES(10,'J',4)
INSERT INTO info VALUES(1,'AA')
INSERT INTO info VALUES(2,'BB')
INSERT INTO info VALUES(3,'CC')
INSERT INTO info VALUES(4,'DD')
INSERT INTO info VALUES(5,'EE')
INSERT INTO info VALUES(6,'FF')
INSERT INTO info VALUES(7,'GG')
INSERT INTO info VALUES(8,'HH')
INSERT INTO info VALUES(9,'II')
INSERT INTO info VALUES(10,'JJ')
复制代码

 

在这个表中存储了有关子节点和父节点的关系,而且你不知道深度有多大,现在的需求是需要找到任意父节点的所有子节点,在使用CTE实现之前还是回顾一下没有CTE之前的写法吧:

复制代码
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
Author: KenshinCui
--
Create date: 2011.12.26
--
Description: 取得所有子节点
--
=============================================
CREATE FUNCTION GetChildren
(
@ID INT
)
RETURNS @tbChildren TABLE(ID INT,Level INT)
AS
BEGIN
DECLARE @Level INT
SET @Level=1
INSERT INTO @tbChildren(ID,Level) SELECT id,@Level FROM dbo.Tree WHERE parentID=@ID
WHILE @@ROWCOUNT<>0
BEGIN
SET @Level=@Level+1
INSERT INTO @tbChildren(ID,Level) SELECT id,@Level FROM dbo.Tree WHERE parentID IN (SELECT ID FROM @tbChildren WHERE Level=(@Level-1) )
END
RETURN
END
GO
复制代码

 

在之前的文章"SQL之树形结构无限级联删除"中事实上也是用的递归来进行解决的,实现原理同上面一样(都是将递归转换为循环,然后通过控制循环结束条件来结束调用)只是上面可能会简洁一些。下面来看使用CTE如何解决这个问题吧:

复制代码
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
Author: KenshinCui
--
Create date: 2011.12.30
--
Description: 取得所有子节点
--
=============================================
CREATE FUNCTION GetChildren
(
@ID INT
)
RETURNS
@tbChildren TABLE
(
ID INT
)
AS
BEGIN

WITH ChildrenCTE(ID)
AS
(
SELECT ID FROM dbo.Tree WHERE parentID=@ID
UNION ALL
SELECT Tree.ID FROM Tree INNER JOIN ChildrenCTE ON ChildrenCTE.ID = Tree.parentID
)
INSERT INTO @tbChildren SELECT Id FROM ChildrenCTE

RETURN
END
GO
复制代码

 

可以看到递归的流程不用手动干预了,结束条件也不用再手动判断,第一次调用SELECT ID FROM dbo.Tree WHERE parentID=@ID将ID放到ChildrenCTE中,第二次调用SELECT Tree.ID FROM Tree INNER JOIN ChildrenCTE ONChildrenCTE.ID = Tree.parentID(当然此时已经有第一次调用得到的ID)如此反复执行此语句(并且注意每次从ChildrenCTE中取值是会排除上次取过的值,否则递归就用于不可能终止了)直到此语句不再返回行为止。这时所有的数据也都已经放到ChildrenCTE中了,如何操作当然就随你了。

需要注意的是CTE默认递归的层次是100,如果无法满足你的需求可以通过OPTION(MAXRECURSION Level)进行设置,例如上面如果希望只查询2层以内的数据只需要在查询ChildrenCTE时加上OPTION(MAXRECURSION2)即可。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
8月前
|
SQL 分布式计算 MaxCompute
odps sql 怎么实现递归查询?
odps sql 怎么实现递归查询?
575 1
|
9月前
|
SQL 存储 分布式计算
ODPS怎么实现SQL中的多层递归?
ODPS怎么实现SQL中的多层递归?
281 1
|
10月前
|
SQL Oracle 关系型数据库
oracle中sql的递归查询运用
oracle中sql的递归查询运用
|
SQL 关系型数据库 数据库
10个高级SQL写法,包括窗口函数、联合查询、交叉查询、递归查询
10个高级SQL写法,包括窗口函数、联合查询、交叉查询、递归查询
182 1
【Sql Server】with as 递归查询的简单使用 以及在视图工具下的使用 注意小事项一
with as 递归查询的简单使用 以及在视图工具下的使用 注意小事项一
222 0
【Sql Server】with as 递归查询的简单使用 以及在视图工具下的使用 注意小事项一
|
SQL 存储 移动开发
PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)|学习笔记
快速学习3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)
413 0
 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)|学习笔记
|
SQL
SQL面试题:类递归计算
最近遇到一个比较有意思的SQL题,看似需要使用递归计算,其实不然,可以使用窗口函数解决
335 0
|
SQL 关系型数据库 数据库
3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(三)|学习笔记
快速学习3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(三)
328 0
|
SQL 数据库