SQL SERVER 2008 新数据库类型hierarchyid
/*--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 为NULL，则返回父级的子级。 如果父级和child1 不为NULL，而child2 为NULL，则返回一个大于child1 的父级的子级。 如果父级和child2 不为NULL，而child1 为NULL，则返回一个小于child2 的父级的子级。 如果父级、child1 和child2 都不为NULL，则返回一个大于child1 且小于child2 的父级的子级。 如果child1 不为NULL 且不是父级的子级，则引发异常。 如果child2 不为NULL 且不是父级的子级，则引发异常。 如果child1 >= child2，则引发异常。*/ --插入一行作为最低级别的后代节点,上面插入的时候就运用的这个特点 --不含参数的GetDescendant 方法插入新行，以将新行节点指定为/1/1/ --上面执行过,此处不执行 DECLARE @PARENTPHASE hierarchyid SELECT @PARENTPHASE= CONVERT(hierarchyid,'/1/') INSERT TEST([PHASE],USERID,USERNAME) VALUES(@PARENTPHASE.GetDescendant(NULL,NULL),1004,'土豆小弟1') --插入一行作为较高级别的后代节点 --使用带有Child1 参数的GetDescendant 方法插入新行 --指定新行的节点将紧跟'/1/1/'节点，成为/1/2/ --上面执行过,此处不执行 DECLARE @PARENTPHASE hierarchyid, @Child1 hierarchyid SELECT @PARENTPHASE= CONVERT(hierarchyid,'/1/') SELECT @Child1 = CAST('/1/1/' AS hierarchyid) INSERT TEST([PHASE],USERID,USERNAME) VALUES(@PARENTPHASE.GetDescendant(@Child1,NULL),1005,'土豆小弟2') --在两个现有节点之间插入一行 --同时使用child1 参数和child2 参数指定新行的节点将成为节点/1/1.1/ DECLARE @PARENTPHASE hierarchyid, @Child1 hierarchyid,@Child2 hierarchyid SELECT @PARENTPHASE= CONVERT(hierarchyid,'/1/') SELECT @Child1 = CAST('/1/1/' AS hierarchyid) SELECT @Child2 = CAST('/1/2/' AS hierarchyid) INSERT TEST([PHASE],USERID,USERNAME) VALUES(@PARENTPHASE.GetDescendant(@Child1,@Child2),1006,'土豆小弟') SELECT [PHASE].ToString() AS [PHASE] ,lvl,userid,username FROM test ORDER BY [PHASE] /* PHASE lvl userid username ------ ------ ---------- ------------ / 0 1001 东升 /1/ 1 1002 土豆 /1/1.1/ 2 1006 土豆小弟3 /1/1/ 2 1004 土豆小弟1 /1/2/ 2 1005 土豆小弟2 /2/ 1 1003 红太狼 (6 行受影响) 节点/1/1.1/高于节点/1/1/低于节点/1/2/,但是属于同一级别 3 GetLevel (): node.GetLevel () 返回一个表示节点在树中的深度的整数。*/太多了请看博文：http://blog.csdn.net/ldslove/archive/2010/05/27/5628007.aspx