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，而 child1 和 child2 为 NULL，则返回父级的子级。
  • 如果父级和 child1 不为 NULL，而 child2 为 NULL，则返回一个大于 child1 的父级的子级。
  • 如果父级和 child2 不为 NULL，而 child1 为 NULL，则返回一个小于 child2 的父级的子级。
  • 如果父级、child1 和 child2 都不为 NULL，则返回一个大于 child1 且小于 child2 的父级的子级。
  • 如果 child1 不为 NULL 且不是父级的子级，则引发异常。
  • 如果 child2 不为 NULL 且不是父级的子级，则引发异常。
  • 如果 child1 >= child2，则引发异常。

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


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

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


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

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


IsDescendantOf( parent ) ：如果这是父级的后代，则返回 true。

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

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


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

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


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

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

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


Write、Read ：用于 CLR ，这里不介绍了。

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


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



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

于是直接上 SQL：

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