SqlServer 递归查询树形数据

by admin on 2019年9月6日
 1 DECLARE @ParentId NVARCHAR(36);
 2 SET @ParentId = '078499bf-dedd-b293-4653-7bf6a2e54cbb';
 3 
 4 WITH [Temp] AS
 5 (
 6     SELECT [A].* FROM [dbo].[Contacts] A WHERE [A].[ParentID] = @ParentId AND [A].[IsDefault] = 1
 7     UNION ALL
 8     SELECT [A].* FROM [dbo].[Contacts] A INNER JOIN [Temp] B ON A.[ParentID] = B.[ObjectID] AND [A].[IsDefault] = 1
 9 )
10 SELECT * FROM [Temp] ORDER BY [Temp].[GlobalSort] ASC;

直接从未留意过数据库处理树形数据的机要,直到有一天朋友问起作者有关树形数据查询的难点时才察觉平素不会,正好今年也要用到递归实行树形数据的查询于是在互连网查了一圈,语法计算如下

追寻悠久,终于达成,记个笔记:

直白未有留神过数据库管理树形数据的首要,直到有一天朋友问起自家有关树形数据查询的标题时才开采一直不会,正好这年也要用到递归举行树形数据的查询于是在网络查了一圈,语法总结如下


SQLSE锐界VE奥迪Q3二零零五事后,mssql开始有了递归查询的办法了。相比起最早阶写存款和储蓄进程依旧写function的点子。那样的主意进一步便捷灵活的。

参照他事他说加以考察文献:

–SqlServer 2005 CTE

参谋文献:

而oracle也许有自带的树形结构递归查询办法,connect by

 

with cte (deptid,dptname,parentid) as (

 

上面我要好写的一段SQL,不难注释下CTE共用表明式的一些用法。
实现对树状结构的根节点和子节点的查询。

一:轻便的树形数据 代码如下:

–开始条件
select a.deptid,a.dptname,a.parentid from base_dept a where a.deptid =
2
union all

一:简单的树形数据 代码如下:

图片 1)

-- with一个临时表(括号中是你要查询的列名)
with temp(ID,PID,Name,curLevel)
as
(
--1:初始查询(这里的PID=-1 在我的数据中是最底层的根节点)
select ID,PID,Name,1 as level from dbo.T_ACL_OU 
where Deleted = 0 and PID = -1     
union all
--2:递归条件
select a.ID,a.PID,a.Name, b.curLevel+1from T_ACL_OU a   --3:这里的临时表和原始数据表都必须使用别名,不然递归的时候不知道查询的是那个表的列
inner join
temp b
on ( a.PID=b.id)  --这个关联关系很重要,一定要理解一下谁是谁的父节点
)
select * from temp   --4:递归完成后 一定不要少了这句查询语句 否则会报错

–CTE每一回递归条件
select a.deptid,a.dptname,a.parentid from base_dept a, cte where
a.parentid = cte.deptid
)

-- with一个临时表(括号中是你要查询的列名)
with temp(ID,PID,Name,curLevel)
as
(
--1:初始查询(这里的PID=-1 在我的数据中是最底层的根节点)
select ID,PID,Name,1 as level from dbo.T_ACL_OU 
where Deleted = 0 and PID = -1     
union all
--2:递归条件
select a.ID,a.PID,a.Name, b.curLevel+1from T_ACL_OU a   --3:这里的临时表和原始数据表都必须使用别名,不然递归的时候不知道查询的是那个表的列
inner join
temp b
on ( a.PID=b.id)  --这个关联关系很重要,一定要理解一下谁是谁的父节点
)
select * from temp   --4:递归完成后 一定不要少了这句查询语句 否则会报错

图片 2代码

功用如图:

–DTE必需紧跟着SQL语句使用。
select deptid,dptname,parentid from cte order by dptname;

意义如图:


图片 3
那边要留意的地点是注释中的 1——4
的有的

 

图片 3
此处要静心的地点是注释中的 1——4
的一部分

— author:jc_liumangtu(【DBA】小七)
— date:    2010-03-30 15:09:42
— version:
— Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86)
—     Oct 14 2005 00:33:37
—     Copyright (c) 1988-2005 Microsoft Corporation
—     Developer Edition on Windows NT 5.1 (Build 2600: Service Pack

二:带缩进的树形数据 代码如下:

附送三个行使CTE的例子:

二:带缩进的树形数据 代码如下:

3)


use test
set nocount on
if object_id(‘Dept’,’U’) is not null
drop table Dept
go
create table Dept(ID int,ParentID int,Name varchar(20))  
insert into Dept select 1,0,’AA’
insert into Dept select 2,1,’BB’
insert into Dept select 3,1,’CC’ 
insert into Dept select 4,2,’DD’ 
insert into Dept select 5,3,’EE’ 
insert into Dept select 6,0,’FF’
insert into Dept select 7,6,’GG’
insert into Dept select 8,7,’HH’
insert into Dept select 9,7,’II’
insert into Dept select 10,7,’JJ’
insert into Dept select 11,9,’KK’

go  
SELECT * FROM Dept;

–查询树状结构某节点的顶头上司全数根节点。
with cte_root(ID,ParentID,NAME)
as
(
    –起初条件
    select ID,ParentID,NAME
    from Dept
    where Name = ‘II’   –列出子节点查询条件
    union all
    –递归条件
    select a.ID,a.ParentID,a.NAME
    from Dept a
    inner join
    cte_root b          –推行递归,这里就要精晓下了
    on a.ID=b.ParentID 
–依据基础表口径查询子节点(a.ID),通过CTE递归找到其父节点(b.ParentID)。
)                       –能够和上边查询子节点的cte_child对比。
select * from cte_root ;

–查询树状结构某节点下的全部子节点。
with cte_child(ID,ParentID,NAME)
as
(
    –初叶条件
    select ID,ParentID,NAME
    from Dept
    where Name = ‘II’ –列出父节点查询条件
    union all
    –递归条件
    select a.ID,a.ParentID,a.NAME
    from Dept a
    inner join
    cte_child b
    on ( a.ParentID=b.ID) 
–依据查询到的父节点(a.Parent),通过CTE递归查询出其子节点(b.ID)
)

select * from cte_child –能够退换从前的询问条件’II’再测量试验结果

ID          ParentID    Name


1           0           AA
2           1           BB
3           1           CC
4           2           DD
5           3           EE
6           0           FF
7           6           GG
8           7           HH
9           7           II
10          7           JJ
11          9           KK

ID          ParentID    NAME


9           7           II
7           6           GG
6           0           FF

ID          ParentID    NAME


9           7           II
11          9           KK

复制代码

图片 5)

在msdn中牵线了CTE的有的范围:

最少有三个定位点成员和三个递归成员,当然,你能够定义四个定位点成员和递归成员,但有所定位点成员必需在递归成员的前方
定位点成员之内必需使用UNION
ALL、UNION、INTETiggoSECT、EXCEPT群集运算符,最终三个定位点成员与递归成员之内必得利用UNION
ALL,递归成员之间也非得采取UNION ALL连接
定位点成员和递归成员中的字段数量和类型必需完全一致
递归成员的FROM子句只好援用三次CTE对象
递归成员中分歧意出现下列项
    SELECT DISTINCT
    GROUP BY
    HAVING
    标量聚合
    TOP
    LEFT、TiggoIGHT、OUTEOdyssey JOIN(允许出现 INNE酷路泽 JOIN)
    子查询

接下去介绍下Oracle里面包车型大巴递归查询办法,connect by prior ,start
with。相对于SqlServer来讲,Oracle的主意尤其简明,简单易懂。很轻松就令人知道其用法。借来作者会用和方面SqlServer一样的数据和结构举办代码演示,和对部分根本字的用法进行解说。

SELECT …..

CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
[START WITH];

上边是代码测量试验:

图片 6)

图片 7代码

–创建表
create table Dept(ID int,ParentID int,Name varchar(20));
–增添测量试验数据,和地点的SqlServer数据一致
insert into Dept  select 1,0,’AA’ from dual;
insert into Dept  select 2,1,’BB’ from dual;
insert into Dept  select 3,1,’CC’  from dual;
insert into Dept  select 4,2,’DD’  from dual;
insert into Dept  select 5,3,’EE’  from dual;
insert into Dept  select 6,0,’FF’ from dual;
insert into Dept  select 7,6,’GG’ from dual;
insert into Dept  select 8,7,’HH’ from dual;
insert into Dept  select 9,7,’II’ from dual;
insert into Dept  select 10,7,’JJ’ from dual;
insert into Dept  select 11,9,’KK’ from dual;
commit;

–查询根节点(父节点)
select * from Dept            –查询基础表
connect by id=prior parentid  –connect
by正是字段的涉及关键字,prior有预先和前的意思,则是坐落哪个字段前,哪个就是递归的上一层
start with name=’II’;         –start
with则是递归的发端地方,也得以用id只怕是parentid。能够修改II的值测验别的数据。

–查询结果
ID    PARENTID    NAME
9      7            II
7      6            GG
6      0            FF

–查询子节点

select * from Dept
connect by prior id=parentid 
–同样的言辞,仅仅改变prior位子,就发生了指向性的转移,就是这里id为递归上一层。
start with name=’II’;

–查询结果
ID    PARENTID    NAME
9       7            II
11    9            KK

–测验结果和SqlServer一致,语句却更加精良,简洁易懂。

复制代码

图片 8)

透过分别对SqlServer和Oracle的测量检验,开掘五个数据库都很好的支持递归查询,相比较之下Oracle的递归查询语句尤其简约易懂,更便于令人知晓。

在做测量检验的时候,SqlServer更方便人民群众的发生测验数据,上边的代码能够复制后重新推行,而Oracle复制施行贰遍能够,重复推行的话,在实行成立表的做事,就能够报错了,原因很简短,Oracle要剖断表存在然后删除后重建的办事用代码完成很困苦。而SqlServer只需求if后drop表再create就消除。所以三种数据库各有优劣。

with temp(ID,PID,Name,curLevel)
as
(
--初始查询
select ID,PID,Name,1 as curLevel from dbo.T_ACL_OU 
where Deleted = 0 and PID = -1     
union all
--递归条件
select a.ID,a.PID, convert(nvarchar(100),CONVERT(nvarchar(100), REPLICATE ('    ', b.curLevel+1)+a.Name)) as Name , b.curLevel+1   --这里的 REPLICATE函数非常重要,用于缩进空格用。不懂得可以在SQLserver中选中后按F1键
from T_ACL_OU a 
inner join
temp b
on ( a.PID=b.id)
)
select ID,PID,Name,curLevel from temp

–CTE
with cte (deptid,dptname,parentid) as (
select a.deptid,a.dptname,a.parentid from base_dept a where a.deptid =
2
union all
select a.deptid,a.dptname,a.parentid from base_dept a, cte where
a.parentid = cte.deptid
)
select a.cdeptid,a.cdeptparentid,count(a.cid) as ccount
from base_excute_compel a,cte
where a.CompelTime >= ‘2010-01-01’
and a.CompelTime <= ‘2013-01-01’
and a.cdeptid in (cte.deptid)
group by a.cdeptid,a.cdeptparentid

with temp(ID,PID,Name,curLevel)
as
(
--初始查询
select ID,PID,Name,1 as curLevel from dbo.T_ACL_OU 
where Deleted = 0 and PID = -1     
union all
--递归条件
select a.ID,a.PID, convert(nvarchar(100),CONVERT(nvarchar(100), REPLICATE ('    ', b.curLevel+1)+a.Name)) as Name , b.curLevel+1   --这里的 REPLICATE函数非常重要,用于缩进空格用。不懂得可以在SQLserver中选中后按F1键
from T_ACL_OU a 
inner join
temp b
on ( a.PID=b.id)
)
select ID,PID,Name,curLevel from temp

效果如图:

效果与利益如图:

图片 9

图片 9

三:查询是不是有子节点

三:查询是不是有子节点

with temp(ID,PID,HandNo,Name,curLevel,pLevel,haveChild)
as
(
--初始查询
select ID,PID,HandNo,Name,1 as level,0 as pLevel,1 as haveChild from dbo.T_ACL_OU 
where Deleted = 0 and PID = -1     
union all
--递归条件
select a.ID,a.PID,a.HandNo,a.Name, b.curLevel+1,b.curLevel,haveChild = (case when exists(select 1 from T_ACL_OU where T_ACL_OU.PID=a.id) then 1 else 0 end)--(select 1 from T_ACL_OU where exists(select 1 from T_ACL_OU where a.PID=b.id)) 
from T_ACL_OU a 
inner join
temp b
on ( a.PID=b.id)
)
select * from temp order by pLevel
with temp(ID,PID,HandNo,Name,curLevel,pLevel,haveChild)
as
(
--初始查询
select ID,PID,HandNo,Name,1 as level,0 as pLevel,1 as haveChild from dbo.T_ACL_OU 
where Deleted = 0 and PID = -1     
union all
--递归条件
select a.ID,a.PID,a.HandNo,a.Name, b.curLevel+1,b.curLevel,haveChild = (case when exists(select 1 from T_ACL_OU where T_ACL_OU.PID=a.id) then 1 else 0 end)--(select 1 from T_ACL_OU where exists(select 1 from T_ACL_OU where a.PID=b.id)) 
from T_ACL_OU a 
inner join
temp b
on ( a.PID=b.id)
)
select * from temp order by pLevel

功用如图:

成效如图:

图片 11

图片 11

那3段代码能够平昔复制利用,修改一下表名和要查询的列名基本上都以通用的,写的相比轻松,要是我们有怎么着意见提议请留言交换多谢。

那3段代码能够直接复制利用,修改一下表名和要询问的列名基本上都以通用的,写的相比较轻易,如若大家有啥样意见提议请留言交流行性发烧谢。

发表评论

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

网站地图xml地图