SQL-图,树,层次结构和递归查询
来源:未知 责任编辑:责任编辑 发表时间:2015-09-17 09:42 点击:次
SQL-图,树,层次结构和递归查询
树:指二叉, 三叉树,属于有向无循环图, 且根结点只有一个
层次结构: 如料表, 属于有向无循环图DAG, 不过和树不同, 根结点可以是一个或多个
图: 通用概念, 主要是看是否有循环, 循环的如道路系统,即无向循环图
案例一:返回指定结点的所有下属(员工系统,树)
With SubsCTE
As
(
--定位点成员, 返回指定的根结点
Select empid, empname, 0 as Lvl
From dbo.Employees
Where empid=@root
www.2cto.com
Union All
--递归成员, 返回下级员工
Select C.empid, C.empname, P.lvl+1
From SubsCTE as P
Join dbo.Employees as C on C.managerID = P.empid
)
Select * from SubsCTE;
案例二: 返回指定结点的所有祖先
With MangersCTE
As
(
Select empid, managerid, empname, 0 as lvl
From dbo.Employees
Where empid = @empid
Union All
Select p.empid, p.ManagerID, p.empname, C.lvl+1
From ManagersCTE as C
Join dbo.Employees as P on C.managerid = p.empid
)
案例三: 返回带有路径的员工链
With SubsCTE
As
(
--定位点成员, 返回指定的根结点
Select empid, empname, 0 as Lvl, ('.' +Cast(empid as varchar(10)) + '.')) as Path
From dbo.Employees www.2cto.com
Where empid=@root
Union All
--递归成员, 返回下级员工
Select C.empid, C.empname, P.lvl+1 , (P.path +Cast(C.empid as varchar(10))+'.') as Path
From SubsCTE as P
Join dbo.Employees as C on C.managerID = P.empid
)
Select * from SubsCTE;
案例四: 检测员工管理关系是否包含循环关系:1->3->1视为循环
思路: 采用员工路径链进行判断, 若包含现有的员工编号则视为循环, 添加循环标识cycle列
With SubsCTE
As
(
--定位点成员, 返回指定的根结点
Select empid, empname, 0 as Lvl, ('.' +Cast(empid as varchar(10)) + '.')) as Path,
--根结点不存在循环
0 as cycle
From dbo.Employees
Where empid=@root
Union All
--递归成员, 返回下级员工
Select C.empid, C.empname, P.lvl+1 , (P.path +Cast(C.empid as varchar(10))+'.') as Path,
--如果父路径包含子级ID则检测到循环
Case When P.Path like '%.'+Cast(c.empid as varchar(10)) +'.%' then 1 else 0 as cycle
From SubsCTE as P www.2cto.com
Join dbo.Employees as C on C.managerID = P.empid
And p.cycle = 0--为防止死循环, 对已经检测出有循环的就不再进行循环了
)
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>