SQL递归查询知多少,实现递归

近期职业中相遇了3个主题素材,要求依照保存的流程数据,塑造流程图。数据库中保存的流水生产线数据是树形结构的,表结构及数量如下图:

不久前做事中遭受了3个标题,须要基于保存的流水生产线数据,创设流程图。数据库中保留的流程数据是树形结构的,表结构及数码如下图:

公用表表明式简单介绍:

公用表表明式简要介绍:

997755.com澳门葡京 1

997755.com澳门葡京 2

公用表表明式 (CTE) 能够以为是在单个 SELECT、INSERT、UPDATE、DELETE 或
CREATE VIEW 语句的实践范围钦点义的最近结果集。CTE
与派生表类似,具体表今后不存款和储蓄为目的,并且只在询问时期有效。与派生表的不一样之处在于,公用表表明式
(CTE) 具有一个首要的优点,这便是能够引用其本人,从而开创递归 CTE。递归
CTE 是一个重新实践早先 CTE
以回到数据子集直到获取完整结果集的公用表表明式。

公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或
CREATE VIEW 语句的施行范围钦命义的权且结果集。CTE
与派生表类似,具体表未来不存款和储蓄为目的,并且只在查询时期有效。与派生表的不一样之处在于,公用表表明式
(CTE) 拥有二个第三的长处,那正是能够引用其本人,从而开创递归 CTE。递归
CTE 是二个重复实施伊始 CTE
以回到数据子集直到获取完整结果集的公用表表明式。

仔细观望表结构,会发现其树形结构的风味:

精心观察表结构,会发现其树形结构的特征:

 

 

  • FFI科雷傲STNODE:标识是还是不是为根节点
  • FSTABLENAME:标志来源单据名称
  • FSID:标识来源单据分录ID
  • FTTABLENAME :标识目的单据名称
  • FTID:标识指标单据分录ID
  • FFI奥迪Q7STNODE:标识是不是为根节点
  • FSTABLENAME:标志来源单据名称
  • FSID:标识来源单据分录ID
  • FTTABLENAME :标志指标单据名称
  • FTID:标识目的单据分录ID

下边先创造一个表,并插入一些数额:

下边先成立一个表,并插入1些数量:

图中的流程为:
出售合同–>出卖订单–>发货布告单–>贩卖出库单

图中的流程为:
出卖合同–>出售订单–>发货公告单–>出卖出库单

create table Role_CTE
(
    Id        int             not null,
    Name    nvarchar(32) not null,
    ParentId    int        not null 
)

insert into Role_CTE(Id,Name,ParentId)
select '1','超级管理员','0' union 
select '2','管理员A','1' union 
select '3','管理员B','2' union 
select '4','会员AA','2' union 
select '5','会员AB','2' union 
select '6','会员BA','3' union 
select '7','会员BB','3' union 
select '8','用户AAA','4' union 
select '9','用户BBA','7'  

-- 创建一个复合聚集索引
create clustered index Clu_Role_CTE_Index
on Role_CTE(Id,ParentId)
with
(
    pad_index=on,
    fillfactor=50,
    drop_existing=off,
    statistics_norecompute=on
)

select * from Role_CTE
create table Role_CTE
(
    Id        int             not null,
    Name    nvarchar(32) not null,
    ParentId    int        not null 
)

insert into Role_CTE(Id,Name,ParentId)
select '1','超级管理员','0' union 
select '2','管理员A','1' union 
select '3','管理员B','2' union 
select '4','会员AA','2' union 
select '5','会员AB','2' union 
select '6','会员BA','3' union 
select '7','会员BB','3' union 
select '8','用户AAA','4' union 
select '9','用户BBA','7'  

-- 创建一个复合聚集索引
create clustered index Clu_Role_CTE_Index
on Role_CTE(Id,ParentId)
with
(
    pad_index=on,
    fillfactor=50,
    drop_existing=off,
    statistics_norecompute=on
)

select * from Role_CTE

第2想到的措施便是把流程数据取回来,然后代码构造流程图。
第3个思路:依据根节点循环往下找,吭呲半天,发现没那么简单。
因为其余二个源头单据都可以频繁下推目的单据:
第一个思路:先找到终极节点,在从终端节点往上找只至根节点为0。
本条思路完结起来也不曾那么复杂,逻辑理清,循环遍历,最后也能兑现结果。(但在大数据量景况下,易导致性能瓶颈。)

率先想到的艺术就是把流程数据取回来,然后代码构造流程图。
首先个思路:依据根节点循环往下找,吭呲半天,发现没那么简单。
因为其余2个源头单据都得以频仍下推指标单据:
其次个思路:先找到终极节点,在从巅峰节点往上找只至根节点为0。
那一个思路完毕起来也绝非那么复杂,逻辑理清,循环遍历,最终也能落到实处结果。(但在大数据量情形下,易造成质量瓶颈。)

997755.com澳门葡京 3

997755.com澳门葡京 4

那三回大家换一个思路,让SQL来替我们做那一复杂的递归查询。

那3遍大家换3个思路,让SQL来替大家做那1复杂的递归查询。

摸索内定节点的装有子孙节点:

搜寻钦赐节点的具备子孙节点:

一、SqlServer 递归查询

1、SqlServer 递归查询

利用普通 sql 语句完毕:

运用普通 sql 语句落成:

一、基本概念

公用表表达式 (CTE) 能够感觉是在单个 SELECT、INSERT、UPDATE、DELETE 或
CREATE VIEW
语句的推行范围内定义的一时半刻结果集。公用表表明式能够包罗对本身的引用,那种表达式称为递归公用表表明式。

  • 始建递归查询。有关详细消息,请参阅应用公用表表明式的递归查询.aspx)。
  • 在不需求经常使用视图时替换视图,也便是说,不必将概念存款和储蓄在元数据中。
  • 启用按从标量嵌套 select
    语句派生的列进行分组,或然按不明朗函数或有外部访问的函数进行分组。
  • 在同等语句中往往引用生成的表。

MSDN上对CTE的介绍
T-SQL查询进阶–详解公用表表明式(CTE)

CTE 的主导语法结构如下:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
--只有在查询定义中为所有结果列都提供了不同的名称时,列名称列表才是可选的。
--运行 CTE 的语句为:
SELECT <column_list> FROM expression_name;

即三个部分:

  1. 公用表表明式的名字(在WITH关键字之后)
  2. SQL递归查询知多少,实现递归。查询的列名(可选)
  3. 紧跟AS之后的SELECT语句(假如AS之后有三个对公用表的询问,则只有首先个查询有效

1、基本概念

公用表表明式 (CTE) 能够以为是在单个 SELECT、INSERT、UPDATE、DELETE 或
CREATE VIEW
语句的实施范围钦赐义的暂且结果集。公用表表明式能够回顾对自家的引用,那种表明式称为递归公用表表明式。

  • 创制递归查询。有关详细音讯,请参阅应用公用表表达式的递归查询.aspx)。
  • 在不须求符合规律使用视图时替换视图,也便是说,不必将概念存款和储蓄在元数据中。
  • 启用按从标量嵌套 select
    语句派生的列举行分组,或许按不精晓函数或有外部访问的函数举行分组。
  • 在同样语句中频繁引用生成的表。

MSDN上对CTE的介绍
T-SQL查询进阶–详解公用表表明式(CTE)

CTE 的骨干语法结构如下:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
--只有在查询定义中为所有结果列都提供了不同的名称时,列名称列表才是可选的。
--运行 CTE 的语句为:
SELECT <column_list> FROM expression_name;

即八个部分:

  1. 公用表表明式的名字(在WITH关键字之后)
  2. 查询的列名(可选)
  3. 紧跟AS之后的SELECT语句(借使AS之后有五个对公用表的询问,则惟有首先个查询有效
declare @level  int
declare @node    int

declare @ResTab table
(
    node    int not null,
    lv        int not null    
)

set @level=0        -- 表示初始的等级
set @node=3            --表示初始的节点ID,即从指定的哪个节点开始查找

insert into @ResTab                -- 为表变量插入初始的数据
select Id,@level 
from Role_CTE 
where Id=@node

while(@@ROWCOUNT>0)
begin
    set @level=@level+1

    insert into @ResTab
    select b.Id,@level 
    from @ResTab a 
    join Role_CTE b on a.node=b.ParentId and lv=@level-1    -- join 等于 inner join(内连接)和自连接
end

select a.node,b.Name,a.lv 
from @ResTab a 
left join Role_CTE b on a.node=b.Id
declare @level  int
declare @node    int

declare @ResTab table
(
    node    int not null,
    lv        int not null    
)

set @level=0        -- 表示初始的等级
set @node=3            --表示初始的节点ID,即从指定的哪个节点开始查找

insert into @ResTab                -- 为表变量插入初始的数据
select Id,@level 
from Role_CTE 
where Id=@node

while(@@ROWCOUNT>0)
begin
    set @level=@level+1

    insert into @ResTab
    select b.Id,@level 
    from @ResTab a 
    join Role_CTE b on a.node=b.ParentId and lv=@level-1    -- join 等于 inner join(内连接)和自连接
end

select a.node,b.Name,a.lv 
from @ResTab a 
left join Role_CTE b on a.node=b.Id

2、出手实践

依据官方网址示例大家很轻便就足以写出CTE语句应用于大家的利用场景:

WITH TEST_CTE 
AS
(
SELECT TBIE.FSTABLENAME,TBIE.FSID,TBIE.FTTABLENAME,TBIE.FTID,TBIE.FROUTEID FROM T_BF_INSTANCEENTRY TBIE
WHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625
UNION ALL
SELECT CTBIE.FSTABLENAME,CTBIE.FSID,CTBIE.FTTABLENAME,CTBIE.FTID,CTBIE.FROUTEID  FROM T_BF_INSTANCEENTRY CTBIE
INNER JOIN TEST_CTE CTE ON CTBIE.FSID=CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME
)
SELECT * FROM TEST_CTE  
--限制递归次数
OPTION(MAXRECURSION 10)

在查询中大家钦定条件参数WHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625,就可以查询到钦点节点的完好流程数据。
里面在与公用表TEST_CTE开展关联时,笔者钦命了多少个标准CTBIE.FSID=CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME,因为差异品类的票子各有1套自增的ID,直接用ID进行关联迭代不可行。

997755.com澳门葡京 5

亟待小心的是OPTION(MAXRECURSION 10)是用来限制递归次数,以制止Infiniti递归导致数据库品质消耗严重。

二、动手执行

基于官方网址示例大家很简短就足以写出CTE语句应用于我们的采纳场景:

WITH TEST_CTE 
AS
(
SELECT TBIE.FSTABLENAME,TBIE.FSID,TBIE.FTTABLENAME,TBIE.FTID,TBIE.FROUTEID FROM T_BF_INSTANCEENTRY TBIE
WHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625
UNION ALL
SELECT CTBIE.FSTABLENAME,CTBIE.FSID,CTBIE.FTTABLENAME,CTBIE.FTID,CTBIE.FROUTEID  FROM T_BF_INSTANCEENTRY CTBIE
INNER JOIN TEST_CTE CTE ON CTBIE.FSID=CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME
)
SELECT * FROM TEST_CTE  
--限制递归次数
OPTION(MAXRECURSION 10)

在查询中大家钦定条件参数WHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625,就能够查询到钦定节点的完整流程数据。
其间在与公用表TEST_CTE开始展览关联时,作者钦点了多少个尺码CTBIE.FSID=CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME,因为分裂类别的单据各有1套自增的ID,直接用ID举办关联迭代不可行。

997755.com澳门葡京 6

内需专注的是OPTION(MAXRECURSION 10)是用来界定递归次数,以制止Infiniti递归导致数据库品质消耗严重。

997755.com澳门葡京 7

997755.com澳门葡京 8

3、扩张:构造递归路径

WITH TEST_CTE 
AS
(
SELECT TBIE.FSTABLENAME,TBIE.FSID,TBIE.FTTABLENAME,TBIE.FTID,TBIE.FROUTEID,Cast(TBIE.FTID as nvarchar(4000)) AS PATH
FROM T_BF_INSTANCEENTRY TBIE
WHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625
UNION ALL
SELECT CTBIE.FSTABLENAME,CTBIE.FSID,CTBIE.FTTABLENAME,CTBIE.FTID,CTBIE.FROUTEID,CTE.PATH+'->'+Cast(CTBIE.FTID as nvarchar(4000)) PATH  
FROM T_BF_INSTANCEENTRY CTBIE
INNER JOIN TEST_CTE CTE ON CTBIE.FSID=CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME
)
SELECT * FROM TEST_CTE  
--限制递归次数
OPTION(MAXRECURSION 10)

遵照上二个查询,增添1列手动拼接递归路线。注意sql大校PATH设置的体系为navarchar(陆仟),在union中,两边的表结构类型必须保持1致,否则会报错定位点类型和递归部分的类型不匹配。可参考此篇博文
缓解CTE定位点类型和递归部分的档次不匹配。

997755.com澳门葡京 9

三、扩展:构造递归路线

WITH TEST_CTE 
AS
(
SELECT TBIE.FSTABLENAME,TBIE.FSID,TBIE.FTTABLENAME,TBIE.FTID,TBIE.FROUTEID,Cast(TBIE.FTID as nvarchar(4000)) AS PATH
FROM T_BF_INSTANCEENTRY TBIE
WHERE TBIE.FTTABLENAME = 'T_SAL_ORDERENTRY' AND TBIE.FTID = 121625
UNION ALL
SELECT CTBIE.FSTABLENAME,CTBIE.FSID,CTBIE.FTTABLENAME,CTBIE.FTID,CTBIE.FROUTEID,CTE.PATH+'->'+Cast(CTBIE.FTID as nvarchar(4000)) PATH  
FROM T_BF_INSTANCEENTRY CTBIE
INNER JOIN TEST_CTE CTE ON CTBIE.FSID=CTE.FTID AND CTBIE.FSTABLENAME = CTE.FTTABLENAME
)
SELECT * FROM TEST_CTE  
--限制递归次数
OPTION(MAXRECURSION 10)

依照上一个询问,扩张壹列手动拼接递归路线。注意sql少将PATH设置的品种为navarchar(伍仟),在union中,两边的表结构类型必须保持1致,不然会报错定位点类型和递归部分的类型不匹配。可参照此篇博文
消除CTE定位点类型和递归部分的类型不相称。

997755.com澳门葡京 10

以上是依照钦赐节点ID(叁),查找父节点ID(即字段
ParentId)等于钦命的节点ID,如果有就插入,并无冕循环。

以上是根据钦赐节点ID(三),查找父节点ID(即字段
ParentId)等于钦命的节点ID,假诺有就插入,并再而三循环。

2、Oracle 递归查询

贰、Oracle 递归查询

PS:lv=@level-一是第1,不然会跻身死循环,功能正是限制只插入1回。

PS:lv=@level-一是关键,不然会跻身死循环,作用正是限制只插入贰遍。

壹、基本概念

Oracle中的递归查询语句为start with…connect by prior,为中序遍历算法。
可参考Oracle 树操作、递归查询(select…start with…connect
by…prior)打探更加多。

997755.com澳门葡京 11

其宗旨语法是:

select colname from tablename
start with 条件1
connect by 条件2
where 条件3;
  • 原则1:
    是根结点的限量语句,当然能够放宽限制条件,以遍历多个根结点,实际正是多棵树。
  • 规则二:是接连条件,个中用PRIOR代表上一条记下。
    比如CONNECT BY PRIOR Id = Parent_Id就是说上一条记下的Id
    是本条记下的Parent_Id
  • 基准3:过滤重临的结果集。

PRIOR关键字

运算符PKoleosIO中华V被停放于等号左右的职位,决定着询问时的探求顺序。

  • PRIOR被置于CONNECT
    BY子句中等号的先头时,则强制从根节点到叶节点的顺序检索,为自顶向下查找。
    如:CONNECT BY PRIOR Id=Parent_Id
  • PIROOdyssey运算符被停放CONNECT BY
    子句中等号的前边时,则强制从叶节点到根节点的次第检索,为自底向上的查找。
    如:CONNECT BY Id=PRIOR Parent_Id

PS:当CONNECT BY后钦点两个连续条件时,各个条件都应钦定PRIOR关键字

1、基本概念

Oracle中的递归查询语句为start with…connect by prior,为中序遍历算法。
可参考Oracle 树操作、递归查询(select…start with…connect
by…prior)问询越多。

997755.com澳门葡京 12

997755.com澳门葡京 ,其基本语法是:

select colname from tablename
start with 条件1
connect by 条件2
where 条件3;
  • 条件壹:
    是根结点的限定语句,当然能够放宽限制条件,以遍历八个根结点,实际就是多棵树。
  • 标准二:是连接条件,在那之中用PRIOR意味着上一条记下。
    比如CONNECT BY PRIOR Id = Parent_Id就是说上一条记下的Id
    是本条记下的Parent_Id
  • 规格三:过滤重回的结果集。

PRIOR关键字

运算符PLX570IOBMWX3被停放于等号左右的职务,决定着询问时的寻觅顺序。

  • PRIOR被置于CONNECT
    BY子句中等号的前方时,则强制从根节点到叶节点的依次检索,为自顶向下查找。
    如:CONNECT BY PRIOR Id=Parent_Id
  • PIRO本田CR-V运算符被平放CONNECT BY
    子句中等号的背后时,则强制从叶节点到根节点的依次检索,为自底向上的查找。
    如:CONNECT BY Id=PRIOR Parent_Id

PS:当CONNECT BY后钦点多个接二连三条件时,种种条件都应钦点PRIOR关键字

设若要求限制循环的次数,即递归的层数,那么只须求在 while
条件里面增多两个限量就能够。如下:

假若急需限制循环的次数,即递归的层数,那么只须求在 while
条件里面加多1个限制就可以。如下:

二、入手实行

理清了用法,大家用Oracle来对查询一下业务流程。

SELECT * FROM T_BF_INSTANCEENTRY 
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME

997755.com澳门葡京 13

该流程为:发售订单–>发货文告单–>出卖出库单–>退货通告单–>发售退货单
里头在钦赐连接条件时,小编内定了七个尺码FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME,因为分化种类的票据各有壹套自增的ID,直接用ID进行关联迭代不可行。

二、入手实行

理清了用法,大家用Oracle来对查询一下业务流程。

SELECT * FROM T_BF_INSTANCEENTRY 
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME

997755.com澳门葡京 14

该流程为:发卖订单–>发货通告单–>发售出库单–>退货公告单–>贩卖退货单
个中在内定连接条件时,小编钦命了五个规范FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME,因为分歧类型的票据各有一套自增的ID,直接用ID实行关联迭代不可行。

declare @level  int
declare @node    int
declare @num    int

declare @ResTab table
(
    node    int not null,
    lv        int not null    
)

set @level=0        -- 表示初始的等级
set @node=3            --表示初始的节点ID,即从指定的哪个节点开始查找
set @num=1        -- 指定递归层级,即循环的次数

insert into @ResTab                -- 为表变量插入初始的数据
select Id,@level 
from Role_CTE 
where Id=@node

while(@@ROWCOUNT>0 and @level<@num)
begin
    set @level=@level+1

    insert into @ResTab
    select b.Id,@level 
    from @ResTab a 
    join Role_CTE b on a.node=b.ParentId and lv=@level-1    -- join 等于 inner join(内连接)和自连接
end

select a.node,b.Name,a.lv 
from @ResTab a 
left join Role_CTE b on a.node=b.Id
declare @level  int
declare @node    int
declare @num    int

declare @ResTab table
(
    node    int not null,
    lv        int not null    
)

set @level=0        -- 表示初始的等级
set @node=3            --表示初始的节点ID,即从指定的哪个节点开始查找
set @num=1        -- 指定递归层级,即循环的次数

insert into @ResTab                -- 为表变量插入初始的数据
select Id,@level 
from Role_CTE 
where Id=@node

while(@@ROWCOUNT>0 and @level<@num)
begin
    set @level=@level+1

    insert into @ResTab
    select b.Id,@level 
    from @ResTab a 
    join Role_CTE b on a.node=b.ParentId and lv=@level-1    -- join 等于 inner join(内连接)和自连接
end

select a.node,b.Name,a.lv 
from @ResTab a 
left join Role_CTE b on a.node=b.Id

三、扩充:构造递归路线

Oracle中提供了SYS_CONNECT_BY_PATH函数用来举办连接路线。

SELECT TBIE.*, SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)  NAME_PATH FROM T_BF_INSTANCEENTRY TBIE
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME

依据上个查询,扩张了1列SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3) NAME_PATH用来拼接递归路线。

997755.com澳门葡京 15

三、扩大:构造递归路线

Oracle中提供了SYS_CONNECT_BY_PATH函数用来进展连接路径。

SELECT TBIE.*, SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)  NAME_PATH FROM T_BF_INSTANCEENTRY TBIE
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME

依据上个查询,扩充了①列SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3) NAME_PATH用来拼接递归路线。

997755.com澳门葡京 16

997755.com澳门葡京 17

997755.com澳门葡京 18

肆、呈现当前节点的根节点

以此时候大家要用到connect_by_root函数,用来记录当前节点的根节点新闻。

SELECT TBIE.*, SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)NAME_PATH, (connect_by_root FTID) ROOT  FROM T_BF_INSTANCEENTRY TBIE
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME

997755.com澳门葡京 19

四、展现当前节点的根节点

那一年我们要用到connect_by_root函数,用来记录当前节点的根节点音讯。

SELECT TBIE.*, SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)NAME_PATH, (connect_by_root FTID) ROOT  FROM T_BF_INSTANCEENTRY TBIE
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME

997755.com澳门葡京 20

本来,假设钦点了循环次数,就足以毫无 while 决断语句的 @@rowcount>0
了。

本来,假如钦定了循环次数,就足以毫不 while 推断语句的 @@rowcount>0
了。

5、Oracle中的with…as语句

Oracle也有with..as 查询语法,一般用来举办子查询,升高查询成效。
语法:

with tempTableName as ( select * from table1 )
select * from tempTableName

拿我们的案例举例正是:

with flow_temp as (
SELECT TBIE.*, SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)NAME_PATH, (connect_by_root FTID) ROOT  FROM T_BF_INSTANCEENTRY TBIE
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME
)

select * from flow_temp

怎么要讲那个吧,大家得以在oracle递归查询后打开筛选啊。

5、Oracle中的with…as语句

Oracle也有with..as 查询语法,一般用来实行子查询,提升查询功用。
语法:

with tempTableName as ( select * from table1 )
select * from tempTableName

拿我们的案例举例就是:

with flow_temp as (
SELECT TBIE.*, SUBSTR(SYS_CONNECT_BY_PATH(FTID,'->'),3)NAME_PATH, (connect_by_root FTID) ROOT  FROM T_BF_INSTANCEENTRY TBIE
START WITH (FTID=100501 AND FTTABLENAME = 'T_SAL_ORDERENTRY')
CONNECT BY  FSID= PRIOR FTID AND FSTABLENAME =PRIOR FTTABLENAME
)

select * from flow_temp

干什么要讲那一个吧,大家能够在oracle递归查询后张开筛选啊。

 

 

使用 SQL CTE 实现:

使用 SQL CTE 实现:

declare @node    int 

set @node=3;

with temp_cte
as
(
    select Id,Name,0 lv        -- 查询出“根节点”,即指定的起始节点
    from Role_CTE 
    where Id=@node 

    union all

    select b.Id,b.Name,a.lv+1 
    from temp_cte a 
    join Role_CTE b on a.Id=b.ParentId
)

select * from temp_cte
declare @node    int 

set @node=3;

with temp_cte
as
(
    select Id,Name,0 lv        -- 查询出“根节点”,即指定的起始节点
    from Role_CTE 
    where Id=@node 

    union all

    select b.Id,b.Name,a.lv+1 
    from temp_cte a 
    join Role_CTE b on a.Id=b.ParentId
)

select * from temp_cte

997755.com澳门葡京 21

997755.com澳门葡京 22

使用 CTE 调控递归的层数,与地点类似。如下:

选拔 CTE 调控递归的层数,与地点类似。如下:

declare @node    int 
declare @num    int

set @node=3;
set @num=1;

with temp_cte
as
(
    select Id,Name,0 lv        -- 查询出“根节点”,即指定的起始节点
    from Role_CTE 
    where Id=@node 

    union all

    select b.Id,b.Name,a.lv+1 
    from temp_cte a 
    join Role_CTE b on a.Id=b.ParentId
                    and a.lv<@num        --控制递归层数
)

select * from temp_cte
declare @node    int 
declare @num    int

set @node=3;
set @num=1;

with temp_cte
as
(
    select Id,Name,0 lv        -- 查询出“根节点”,即指定的起始节点
    from Role_CTE 
    where Id=@node 

    union all

    select b.Id,b.Name,a.lv+1 
    from temp_cte a 
    join Role_CTE b on a.Id=b.ParentId
                    and a.lv<@num        --控制递归层数
)

select * from temp_cte

997755.com澳门葡京 23

997755.com澳门葡京 24

 

 

探究钦定节点的兼具祖先节点:

寻觅内定节点的保有祖先节点:

选择普通 sql 语句达成:

行使普通 sql 语句实现:

declare @level  int
declare @node    int
declare @num    int

declare @ResTab table
(
    node    int not null,
    lv        int not null    
)

set @level=0    -- 表示初始的等级
set @node=8            --表示初始的节点ID,即从指定的哪个节点开始查找
set @num=2        -- 指定递归层级,即循环的次数

while(@level<=@num and @node is not null)    -- 如果为空就表示没有查到父级了
begin

    insert into @ResTab
    select @node,@level

    set @level=@level+1

    select @node=ParentId 
    from Role_CTE 
    where Id=@node

end

select a.node,b.Name,a.lv 
from @ResTab a 
left join Role_CTE b on a.node=b.Id
declare @level  int
declare @node    int
declare @num    int

declare @ResTab table
(
    node    int not null,
    lv        int not null    
)

set @level=0    -- 表示初始的等级
set @node=8            --表示初始的节点ID,即从指定的哪个节点开始查找
set @num=2        -- 指定递归层级,即循环的次数

while(@level<=@num and @node is not null)    -- 如果为空就表示没有查到父级了
begin

    insert into @ResTab
    select @node,@level

    set @level=@level+1

    select @node=ParentId 
    from Role_CTE 
    where Id=@node

end

select a.node,b.Name,a.lv 
from @ResTab a 
left join Role_CTE b on a.node=b.Id

997755.com澳门葡京 25

997755.com澳门葡京 26

使用 SQL CTE 实现:

使用 SQL CTE 实现:

declare @node    int 
declare @num    int

set @node=8;
set @num=2;

with temp_cte
as
(
    select Id,Name,ParentId,0 lv        -- 查询出“根节点”,即指定的起始节点
    from Role_CTE 
    where Id=@node 

    union all

    select b.Id,b.Name,b.ParentId,a.lv+1 
    from temp_cte a 
    join Role_CTE b on a.ParentId=b.Id
                    and a.lv < @num        --控制递归层数
)

select * from temp_cte
declare @node    int 
declare @num    int

set @node=8;
set @num=2;

with temp_cte
as
(
    select Id,Name,ParentId,0 lv        -- 查询出“根节点”,即指定的起始节点
    from Role_CTE 
    where Id=@node 

    union all

    select b.Id,b.Name,b.ParentId,a.lv+1 
    from temp_cte a 
    join Role_CTE b on a.ParentId=b.Id
                    and a.lv < @num        --控制递归层数
)

select * from temp_cte

997755.com澳门葡京 27

997755.com澳门葡京 28

 

 

参考:

参考:

 

 

相关文章

发表评论

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

*
*
Website