代码之家 › 专栏 › 技术社区 › Gabriel Santos Reis

获取父SQL Server变量hierarchyid

hierarchyid parent sql-server

Gabriel Santos Reis · 技术社区 · 9 年前

如何使用sql by id(EmployeeID)中hierarchyid类型变量的变量获取表中的父节点? 这是我的桌子

CREATE TABLE Employee
(
   Node hierarchyid PRIMARY KEY CLUSTERED,
   EmployeeID int UNIQUE NOT NULL,
   EmpName varchar(20) NOT NULL,
   Title varchar(20) NULL
) ;
GO

3 回复 | 直到 9 年前

Ben Thul 9 年前

这将使您成为@h中指定id的直接管理者

declare @h hierarchyid;

select *
from dbo.Employee
where Node = @h.GetAncestor(1);

虽然这会让你所有的经理都上链:

select *
from dbo.Employee
where @h.IsDescendantOf(Node) = 1

对于最后一个节点,节点被视为其自身的后代。如果不希望查询返回@h中指定的雇员,请添加谓词 and Node <> @h 到where子句。

编辑:

再看一遍你的问题,你似乎想通过一个EmployeeID并找到经理。这是:

select m.*
from dbo.Employee as e
join dbo.Employee as m
   on e.Node.GetAncestor(1) = m.Node
where e.EmployeeID = <yourIDHere>

Gabriel Santos Reis 9 年前

我找到了解决问题的简单方法:

SELECT EmployeeID 
FROM Employee
WHERE [Node] IN (
               SELECT [Node].GetAncestor(1).ToString()
               FROM Employee
               WHERE EmployeeID=4
               )

谢谢你的回答!!!

Reeya Oberoi 9 年前

以下内容将帮助您理解层次结构并获取parent_id(在下面的示例中,我将其称为ManagerID)。

/*

Here is the problem definition:
1. Employees table contains the following columns a) EmployeeId, b) EmployeeName c) ManagerId 
2. If an EmployeeId is passed, the query should list down the entire organization hierarchy i.e who is the manager of the EmployeeId passed and who is managers manager and so on till full hierarchy is listed.

Here are the two scenarios - 
Scenario 1: If we pass Javed's EmployeeId to the query, then it should display the organization hierarchy starting from Javed.

Scenario 2: If we pass Nussenbaum's EmployeeId to the query, then it should display the organization hierarchy starting from Nussenbaum.

*/

--Here is the test data

IF OBJECT_ID ('tempdb..#Employees') IS NOT NULL
DROP TABLE #Employees

Create table #Employees
(
EmployeeID int primary key identity,
EmployeeName nvarchar(50),
ManagerID int foreign key references #Employees(EmployeeID)
)
GO


Insert into #Employees values ('Sonal', NULL)
Insert into #Employees values ('Angus', NULL)
Insert into #Employees values ('Nik', NULL)
Insert into #Employees values ('Abu', NULL)
Insert into #Employees values ('Nussenbaum', NULL)
Insert into #Employees values ('Anirudh', NULL)
Insert into #Employees values ('Javed', NULL)
Insert into #Employees values ('Ron', NULL)
Insert into #Employees values ('Matt', NULL)
Insert into #Employees values ('Nikhil', NULL)
GO

Update #Employees Set ManagerID = 8 Where EmployeeName IN ('Angus', 'Nik', 'Nussenbaum')
Update #Employees Set ManagerID = 2 Where EmployeeName IN ('Matt', 'Anirudh')
Update #Employees Set ManagerID = 3 Where EmployeeName IN ('Abu')
Update #Employees Set ManagerID = 5 Where EmployeeName IN ('Sonal', 'Nikhil')
Update #Employees Set ManagerID = 4 Where EmployeeName IN ('Javed')
GO

--Here is the SQL that does the job

Declare @ID int ;
Set @ID = 7;

WITH EmployeeCTE AS
(
Select EmployeeId, EmployeeName, ManagerID
From #Employees
Where EmployeeId = @ID

UNION ALL

Select #Employees.EmployeeId , #Employees.EmployeeName, #Employees.ManagerID
From #Employees
JOIN EmployeeCTE
ON #Employees.EmployeeId = EmployeeCTE.ManagerID
)

Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeId