Flatten hierarchyid in SQL Server 2008

最新推荐文章于 2024-03-28 17:23:07 发布

浪菜叶

最新推荐文章于 2024-03-28 17:23:07 发布

阅读量472

点赞数

分类专栏： BI SQL Server 文章标签： sql server 数据库微软

本文链接：https://blog.csdn.net/langcaiye/article/details/5613263

版权

BI 同时被 2 个专栏收录

22 篇文章 1 订阅

订阅专栏

SQL Server

2 篇文章 0 订阅

订阅专栏

在SQL Server 2008中引入了hierarchyid来处理树状结构。下面简单就以AdventureWorks(无hierarchyid)和AdventureWorks2008(有hierarchyid)里的HumanResources.Employee为例，来说明一下在新的hierarchyid中如何进行flat化的dimension抽取。

AdventureWorks是微软为SQL Server提供的数据库虚拟案例。

AdventureWorks中所要的结果集代码如下:

select L4.NationalIDNumber, L4.Title, L3.NationalIDNumber, L3.Title, L2.NationalIDNumber, L2.Title, L1.NationalIDNumber, L1.Title, L0.NationalIDNumber, L0.Title

from HumanResources.Employee L4, HumanResources.Employee L3, HumanResources.Employee L2, HumanResources.Employee L1, HumanResources.Employee L0

where L4.ManagerID = L3.EmployeeID and L3.ManagerID = L2.EmployeeID and L2.ManagerID = L1.EmployeeID and L1.ManagerID = L0.EmployeeID

AdventureWorks2008中的代码如下:

select L4.NationalIDNumber, L4.JobTitle, L3.NationalIDNumber, L3.JobTitle, L2.NationalIDNumber, L2.JobTitle, L1.NationalIDNumber, L1.JobTitle, L0.NationalIDNumber, L0.JobTitle

from HumanResources.Employee L0, HumanResources.Employee L1, HumanResources.Employee L2, HumanResources.Employee L3, HumanResources.Employee L4

where L4.OrganizationNode.GetAncestor(1)=L3.OrganizationNode and L3.OrganizationNode.GetAncestor(1)=L2.OrganizationNode and L2.OrganizationNode.GetAncestor(1)=L1.OrganizationNode and L1.OrganizationNode.GetAncestor(1)=L0.OrganizationNode

GetAncestor()是HierarchyNode类里其中的一个方法。更多hierarchyid数据类型方法引用可参考以下链接：

http://technet.microsoft.com/zh-cn/library/bb677193.aspx