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
[解决办法]
sf 来
[解决办法]
学习是必须的
[解决办法]
这个此前我也有在整理，觉得查询方便，就是插入的时候麻烦。
[解决办法]
我是来鄙视东升哥的.
这回才放50
[解决办法]
学习！
[解决办法]
jfjf 看完了 不错的数据类型 谢谢分享 同来享你的分 其中最欣赏你的那句插入小弟 反过来效果更佳 小弟插入
[解决办法]
谢谢不错的数据类型
------解决方案--------------------
学习@@
谢谢@
[解决办法]
赞 .....
[解决办法]
很好不错哦看看学习
[解决办法]
ssssssssssss
[解决办法]
dddddddd
[解决办法]
这个有点麻烦，我觉得在程序里用datatable的特性做个递归就好了啊，而且字段只需要id,name,parentid就可以了,速度也很快的
[解决办法]
收藏了 . ths
[解决办法]
学习。。。
[解决办法]
学习是必须的
[解决办法]
发现还需学习啊
[解决办法]
学。。。
[解决办法]
还需学习啊
[解决办法]
hahahhaaaaaaaaaaaaaaaaa
[解决办法]
努力学习
[解决办法]
学习。
[解决办法]
aaaaaaaaaaaaaaaaaaaaaaaaa
[解决办法]
向东升哥致敬
[解决办法]
对于 我这种菜鸟来说 有点困难
[解决办法]
感觉插入时麻烦点。。
[解决办法]
厉害啊 呵呵
[解决办法]
good
[解决办法]
学习学习
[解决办法]
学习。。。
[解决办法]
用SQL2005，学习中。。。
[解决办法]
学习了
[解决办法]
学习了，每太看懂。
[解决办法]
感觉不是太好用 这个数据类型
http://topic.csdn.net/u/20080514/15/af788986-0ef6-4d83-8e9d-4cb97e2831c8.html
[解决办法]
还是要 膜拜下 东升哥
[解决办法]
受教了
[解决办法]
不错的啊 新东西 JF
[解决办法]
新东东！学习学习！
[解决办法]
来看看，学习下。
[解决办法]
学习是必须的
[解决办法]
ooooooo。。。。。。
[解决办法]
学习数据库，很有用
[解决办法]
dtg
[解决办法]
hhhhhh
[解决办法]
赞一个。。。。
[解决办法]
相当的不错，谢谢！
[解决办法]
高手，人才
[解决办法]
学习学习
[解决办法]
收藏，学习。
[解决办法]
该回复于2010-12-02 14:13:36被版主删除