SQL Server hierarchyid数据类型

SQL Server 专栏收录该内容
0 篇文章 0 订阅
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



  • 2
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值