/*

--2010-05-27(东升)

 

 

 

SQL SERVER 2008 新数据库类型hierarchyid

       

    SQL SERVER 2008引入了新的hierarchyid数据类型,可以用它来做本地存储并且在树层次

结构中管理其位置.只用这个函数能简洁地表示层次结构中的位置.该函数提供的一些内置的函

数方法可以操作和遍历层次结构,使得存储和查询分层数据更为容易,而不需要像那样通过

CTE递归来获得.

 

    该类型其实是一个CLR自定义数据类型依次打开:数据库->系统数据库->master->可编程性

->类型->系统数据类型->CLR数据类型->hierarchyid,可以看到该数据类型.

    

    于hierarchyid有关的一些函数主要有:

    

    GetAncestor ：取得某一个级别的祖先

    GetDescendant ：取得某一个级别的子代

    GetLevel ：取得级别

    GetRoot ：取得根

    IsDescendantOf ：判断某个节点是否为某个节点的子代

    Parse ：将字符串转换为hierarchyid。该字符串的格式通常都是/1/这样的

    Read ：Read 从传入的BinaryReader 读取SqlHierarchyId 的二进制表示形式，并将

           SqlHierarchyId 对象设置为该值。不能使用Transact-SQL 调用Read。请改

           为使用CAST 或CONVERT。

    GetReparentedValue ：可以用来移动节点（或者子树）

    ToString ：将hierarchyid转换为字符串，与parse正好相反

    Write : 将SqlHierarchyId 的二进制表示形式写出到传入的BinaryWriter 中。无法通

           过使用Transact-SQL 来调用Write。请改为使用CAST 或CONVERT。

 

 

    以下就该新类型做一些演示

*/

 

    USE TESTDB

    GO

    

    CREATE TABLE TEST(

           [PHASE] hierarchyid NOT NULL,

           LVL AS [PHASE].GetLevel(),

           USERID INT NOT NULL,

           USERNAME VARCHAR(50) NOT NULL

           )

    

    --接着插入一个根

    

    INSERT TEST([PHASE],USERID,USERNAME)

    VALUES('/',1001,'东升')--'/'被用来表示层次的根,会自动转换成二进制格式

    

    

    SELECT * FROM TEST

    

/*  结果

 

    PHASE  LVL    USERID     USERNAME

    ------ ------ ---------  ----------

    0x     0      1001       东升

 

    (1 行受影响)

    

    查询后应该发现'/'被重新定义成x十六进制值.

    

    使用斜杠字符来表示层次路径,一个表示的是根,用斜杠分隔的整数值来组成连续的层次.

    

    

    插入小弟

*/

    INSERT TEST([PHASE],USERID,USERNAME)

    VALUES('/1/',1002,'土豆')

    

    INSERT TEST([PHASE],USERID,USERNAME)

    VALUES('/2/',1003,'红太狼')

    --以上条数据时同级别的

    

    

    SELECT * FROM TEST

/*

    PHASE      LVL    USERID      USERNAME

    -------    ------ ---------  -----------

    0x         0      1001        东升

    0x58       1      1002        土豆

    0x68       1      1003        红太狼

 

    (3 行受影响)

    

*/

    

    

    --同样可以使用GetDescendant方法来填充[PHASE]

    DECLARE    @PARENTPHASE hierarchyid, @Child1 hierarchyid

    SELECT @PARENTPHASE= CONVERT(hierarchyid,'/1/')

    

    INSERT TEST([PHASE],USERID,USERNAME)

    VALUES(@PARENTPHASE.GetDescendant(NULL,NULL),1004,'土豆小弟1')

    

    SELECT @Child1 = CAST('/1/1/' AS hierarchyid)

    INSERT TEST([PHASE],USERID,USERNAME)

    VALUES(@PARENTPHASE.GetDescendant(@Child1,NULL),1005,'土豆小弟2')

    

    SELECT [PHASE].ToString() AS [PHASE] ,LVL,USERID,USERNAME FROM TEST

    ORDER BY [PHASE]

    

/*

    PHASE      LVL    USERID      USERNAME

    ---------- ------ ---------  ---------

    /          0      1001        东升

    /1/        1      1002        土豆

    /1/1/      2      1004        土豆小弟1

    /1/2/      2      1005        土豆小弟2

    /2/        1      1003        红太狼

 

    (5 行受影响)

 

    查询中的中ToString方法可以显示hierarchyid类型的字符串表示,而不是十六进制值.

    该方法下面会经常用到.

    

    

    下面演示一下上面提到的函数方法(注意方法的调用要注意大小写):

    

    

1 GetAncestor: child.GetAncestor ( n ) 返回指定的祖先. 

       用于测试输出中的每个节点是否将当前节点作为指定级别的祖先。

       如果传递的数字大于GetLevel()，则返回NULL。

       如果传递的是负数，则引发异常

 

*/

 

    SELECT PHASE.GetAncestor(2).ToString()AS Ancestor 

           --把对应的二级祖先的层次显示为字符串

    ,PHASE.ToString() as CURPHASE

    ,USERNAME

    FROM TEST

    WHERE LVL>=2 --去除级别小于的行           

 

/*

    Ancestor       CURPHASE   USERNAME

    -----------   ---------  ----------

    /              /1/1/      土豆小弟1

    /              /1/2/      土豆小弟2

 

    (2 行受影响)

    

    注意GetAncestor(0)返回的是节点本身

 

 

2 GetDescendant: parent.GetDescendant ( child1 , child2 ) 返回子节点

    child1:NULL 或当前节点的子节点的hierarchyid。

    child2:NULL 或当前节点的子节点的hierarchyid。

 

    如果父级为NULL，则返回NULL。

    如果父级不为NULL，而child1 和child2 为NUL