hierarchyid 这个数据类型是 MSSQL 2008 开始支持的一个新的数据类型,用于表示树形结构中的位置。

当时以为使用 hierarchyid 可以做到无限分层的分类,其实并不。MSDN 上就提示说:hierarchyid 类型中所用的编码限制为 892 字节。所以如果节点字节数可能会超过 892 字节,就无法使用 hierarchyid 数据类型。MSDN的介绍

 

先墨迹下拷贝介绍 hierarchyid 的函数。hierarchyid 列出树形的全部子树数量的 SQL 语句在最后。

hierarchyid 系统类型提供了 10 个函数来实现各种功能,分别是:

 

GetAncestor( n ) :返回表示 this 的第 n 个祖先的 hierarchyid

如果传递的数字大于 GetLevel() ,则返回 NULL。如果传递的是负数,则引发异常。

 

GetDescendant( child1 , child2 ) :返回父级的一个子节点。

返回作为父节点的后代的一个子节点。

  • 如果父级为 NULL,则返回 NULL
  • 如果父级不为 NULL,而 child1child2NULL,则返回父级的子级。
  • 如果父级和 child1 不为 NULL,而 child2NULL,则返回一个大于 child1 的父级的子级。
  • 如果父级和 child2 不为 NULL,而 child1NULL,则返回一个小于 child2 的父级的子级。
  • 如果父级、child1child2 都不为 NULL,则返回一个大于 child1 且小于 child2 的父级的子级。
  • 如果 child1 不为 NULL 且不是父级的子级,则引发异常。
  • 如果 child2 不为 NULL 且不是父级的子级,则引发异常。
  • 如果 child1 >= child2,则引发异常。

GetDescendant 是确定的,因此,如果使用相同的输入调用 GetDescendant(),它将始终生成相同的输出。不过,生成的子级的确切身份可能因其与其他节点的关系而异。

 

GetLevel() :返回一个表示节点 this 在树中的深度的整数。

用于确定一个或多个节点的级别或者筛选指定级别的成员的节点。 层次结构的根节点为级别 0。

 

GetRoot() :返回层次结构树的根。 GetRoot() 是静态方法。

用于确定层次结构树中的根节点。

 

IsDescendantOf( parent ) :如果这是父级的后代,则返回 true

对于以父级为根的子树中的所有节点,返回 true;对于其他所有节点,返回 false

父级被视为其本身的后代。

 

Parse() :Parsehierarchyid 的规范字符串表示形式转换为 hierarchyid 值。 当发生从字符串类型到 hierarchyid 的转换时,将隐式调用 Parse。 作用与 ToString 相反。 Parse() 是静态方法。

ToString() :返回具有 this 逻辑表示形式的字符串。 进行从 hierarchyid 到字符串类型的转换时将隐式调用 ToString

 

GetReparentedValue( oldRoot , newRoot ) :返回一个节点,其相对于根的路径是指向 newRoot 的路径,后跟从 oldRootthis 的路径。

可用于通过将节点从 oldRoot 移动到 newRoot 来修改树。

PS:感觉这货很鸡肋啊,因为更新的时候要有 newRoot ,还要有 oldRoot ,最主要的是还要有 WHERE 子句,感觉没意义啊,用 UPDATE XXX SET Id='/1/1/1/' WHERE Id='/1/1/' 不是更好么…_(:3」∠)_

 

WriteRead :用于 CLR ,这里不介绍了。

除了 ReadWrite 两个方法是用于 CLR 的,无法在 T-SQL 中直接调用,其他 8 个都可以直接在 T-SQL 中使用,注意函数名大小写敏感

 

前面博主简要地介绍了下 hierarchyid 的函数,博主这里推荐一下 这篇 博文,有比较详细的介绍。

 

 

之后,博主犯二了,实现“hierarchyid 列出树形的全部子树数量”这个简单的方法竟然用了一个晚上….简直浪费时间浪费生命…

于是直接上 SQL:

--这是表,简化下就两个字段
CREATE TABLE [dbo].[C](
	[Id] [hierarchyid] NOT NULL,
	[Name] [varchar](40) NOT NULL,
)

--这就是“哔”了狗的SQL语句
SELECT Id.ToString() AS Id
       ,GroupName
       ,(SELECT COUNT(Id)-1 FROM C WHERE Id.IsDescendantOf(CC.Id)=1) AS Count 
FROM C AS CC

关于索引,只在 Id 上建立了唯一聚集索引,在 Name 上建立了不唯一非聚集索引。

在执行计划中,很可惜有 36% 开销用在 Index Scan ( NonClustered ) 上,另外有 63% 用在 Clustered Index Seek ( Clustered ) 上。

The End


原创文章,转载请以链接形式注明出处:https://blog.ttionya.com/article-1126.html