什么是查看来自自引用表的层次结构数据 [英] 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屋！