CREATE TABLE Employee
(
EmployeeID int not null,
EmployeeNode hierarchyid not null,
EmployeeName varchar(10) not null,
EmployeeTitle varchar(10) not null
);
INSERT INTO Employee(EmployeeID, EmployeeNode, EmployeeName, EmployeeTitle)
values
(1,hierarchyid::Parse('/1/'),'王大拿','董事长'),
(2,hierarchyid::Parse('/1/1/'),'刘大脑袋','总经理'),
(3,hierarchyid::Parse('/1/1/1/'),'小李','员工'),
(4,hierarchyid::Parse('/1/1/2/'),'王天来','员工'),
(5,hierarchyid::Parse('/1/2/'),'赵四','总经理');
SELECT EmployeeID, EmployeeNode, EmployeeName, EmployeeTitle
FROM Employee
ORDER BY EmployeeID;
--查层级
SELECT EmployeeID, EmployeeNode, EmployeeName, EmployeeTitle,
CAST( EmployeeNode AS varchar) AS EmployeeNode2,
EmployeeNode.GetLevel() AS EmployeeLevel
FROM Employee
ORDER BY EmployeeNode.GetLevel();
--查子级
SELECT EmployeeID, EmployeeNode, EmployeeName, EmployeeTitle
FROM Employee
WHERE EmployeeNode.IsDescendantOf
(
CONVERT(varchar,(SELECT EmployeeNode FROM Employee WHERE EmployeeName = '刘大脑袋'))
)
= 1;
--查父级
SELECT EmployeeID, EmployeeNode, EmployeeName, EmployeeTitle
FROM Employee
WHERE hierarchyid::Parse
(
CONVERT(varchar,(SELECT EmployeeNode FROM Employee WHERE EmployeeName = '王天来'))
).GetAncestor(1) = EmployeeNode
--查父级(所有)
SELECT EmployeeID, EmployeeNode, EmployeeName, EmployeeTitle
FROM Employee
WHERE hierarchyid::Parse
(
CONVERT(varchar,(SELECT Employeenode FROM Employee WHERE EmployeeName = '王天来'))
).IsDescendantOf(EmployeeNode) = 1
SQL Server hierarchyid数据类型
最新推荐文章于 2025-02-15 19:55:13 发布