什么是查看来自自引用表的层次结构数据 [英] What is the Query for see hierarchy data from self-referencing tables
本文介绍了什么是查看来自自引用表的层次结构数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有表EmployeeMaster赞
EmpID名称ReportingEmployeeID
EMP001 A EMP002
EMP002 B EMP004
EMP003 C EMP008
EMP004 D EMP003
EMP008 E EMP233
EMP233 F EMP122
EMP122 G EMP006
现在我希望看到员工的层次结构,比如第一个EmpID,然后是他的ReportingEmpId,然后是他的ReportingEmpId等等.....
来自上表我希望输出像
<前lang =SQL> EMP001
EMP002
EMP004
EMP003
EMP008
EMP233
EMP122
EMP006
这个层次结构的查询是什么
解决方案
有大量的有关层级查询的文章。尝试下面的谷歌搜索
分层查询CTE SQL Server [ ^ ]
WITH UserCTE AS(
SELECT EmpCode,EmpName,ReportingManagerCode,0 AS步骤
来自EMP_MASTER
WHERE ReportingManagerCode ='EMP001'
UNION ALL
SELECT mgr.EmpCode,mgr.EmpName,mgr.ReportingManagerCode,usr.steps +1 AS步骤
来自UserCTE AS usr
INNER JOIN EMP_MASTER AS mgr
ON usr.ReportingManagerCode = mgr.EmpCode
)
SELECT *来自UserCTE AS u
I have table EmployeeMaster like
EmpID Name ReportingEmployeeID
EMP001 A EMP002
EMP002 B EMP004
EMP003 C EMP008
EMP004 D EMP003
EMP008 E EMP233
EMP233 F EMP122
EMP122 G EMP006
now i want to see Hierarchy of employee like first EmpID then his ReportingEmpId then his ReportingEmpId and so on.....
from above table i want output like
EMP001
EMP002
EMP004
EMP003
EMP008
EMP233
EMP122
EMP006
What is the Query for this Hierarchy
解决方案
There are tons of articles available regarding hierarchal queries. Try below google search
Hierarchical query CTE SQL Server[^]
WITH UserCTE AS (
SELECT EmpCode, EmpName, ReportingManagerCode,0 AS steps
FROM EMP_MASTER
WHERE ReportingManagerCode = 'EMP001'
UNION ALL
SELECT mgr.EmpCode, mgr.EmpName, mgr.ReportingManagerCode, usr.steps +1 AS steps
FROM UserCTE AS usr
INNER JOIN EMP_MASTER AS mgr
ON usr.ReportingManagerCode = mgr.EmpCode
)
SELECT * FROM UserCTE AS u
这篇关于什么是查看来自自引用表的层次结构数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文