-- 测试环境
CREATE TABLE [dbo].[Folder]
(
[FolderNode] HIERARCHYID NOT NULL,
[Description] NVARCHAR(50) NOT NULL
);
GO
--Add node A
DECLARE @folderNode HIERARCHYID = HIERARCHYID::GetRoot();
INSERT INTO [dbo].[Folder]
VALUES (@folderNode, 'A')--Add node B (first child, so NULL, NULL)
DECLARE @childFolderNode HIERARCHYID = @folderNode.GetDescendant(NULL, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'B')--Add node E (second child, so Child 1, NULL)
SET @childFolderNode = @folderNode.GetDescendant(@childFolderNode, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'E')--Add node F (third child, so Child 2, NULL)
SET @childFolderNode = @folderNode.GetDescendant(@childFolderNode, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'F')
SELECT @folderNode = FolderNode
FROM [dbo].[Folder]
WHERE Description = 'B'
SET @childFolderNode = @folderNode.GetDescendant(NULL, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'C')--Add node D (second child, so Child 1, NULL)
SET @childFolderNode = @folderNode.GetDescendant(@childFolderNode, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'D')
SET @childFolderNode = @childFolderNode.GetDescendant(NULL, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'G')INSERT dbo.Folder (
FolderNode,
Description
)
SELECT FolderNode.GetDescendant(NULL,NULL).ToString(),'H'
FROM Folder
WHERE Description = 'E'
-- 查询测试表
SELECT *,FolderNode.ToString() FROM dbo.Folder--FolderNode Description
0x A /
0x58 B /1/
0x68 E /2/
0x78 F /3/
0x5AC0 C /1/1/
0x5B40 D /1/2/
0x5B56 G /1/2/1/
0x6AC0 H /2/1/--问题
将B的父亲改为E,当然B的后代也要相应更新。2005时代的老方法就别粘贴啦,谢谢。另外给大家介绍一本书,Intorducing Microsoft SQL Server 2008,这本书只讲SQL Server 2008的新特性,不罗嗦,值得一读,网上很容易搜到。大家要读过就别说我out啦,我刚开始看。
CREATE TABLE [dbo].[Folder]
(
[FolderNode] HIERARCHYID NOT NULL,
[Description] NVARCHAR(50) NOT NULL
);
GO
--Add node A
DECLARE @folderNode HIERARCHYID = HIERARCHYID::GetRoot();
INSERT INTO [dbo].[Folder]
VALUES (@folderNode, 'A')--Add node B (first child, so NULL, NULL)
DECLARE @childFolderNode HIERARCHYID = @folderNode.GetDescendant(NULL, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'B')--Add node E (second child, so Child 1, NULL)
SET @childFolderNode = @folderNode.GetDescendant(@childFolderNode, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'E')--Add node F (third child, so Child 2, NULL)
SET @childFolderNode = @folderNode.GetDescendant(@childFolderNode, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'F')
SELECT @folderNode = FolderNode
FROM [dbo].[Folder]
WHERE Description = 'B'
SET @childFolderNode = @folderNode.GetDescendant(NULL, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'C')--Add node D (second child, so Child 1, NULL)
SET @childFolderNode = @folderNode.GetDescendant(@childFolderNode, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'D')
SET @childFolderNode = @childFolderNode.GetDescendant(NULL, NULL);
INSERT INTO [dbo].[Folder]
VALUES (@childFolderNode, 'G')INSERT dbo.Folder (
FolderNode,
Description
)
SELECT FolderNode.GetDescendant(NULL,NULL).ToString(),'H'
FROM Folder
WHERE Description = 'E'
-- 查询测试表
SELECT *,FolderNode.ToString() FROM dbo.Folder--FolderNode Description
0x A /
0x58 B /1/
0x68 E /2/
0x78 F /3/
0x5AC0 C /1/1/
0x5B40 D /1/2/
0x5B56 G /1/2/1/
0x6AC0 H /2/1/--问题
将B的父亲改为E,当然B的后代也要相应更新。2005时代的老方法就别粘贴啦,谢谢。另外给大家介绍一本书,Intorducing Microsoft SQL Server 2008,这本书只讲SQL Server 2008的新特性,不罗嗦,值得一读,网上很容易搜到。大家要读过就别说我out啦,我刚开始看。
解决方案 »
- 求助:将MDB数据库导入到SQL的存储过程
- 求助 sql 语句
- Reporting Services 2005 报表问题 (分不够再给)
- 我先安装了vs2005 附带安装了sql express,现在安装sql develop,在线等
- 什么是 查询成本 ? 如何查看某个sql 语句的执行时间 ? 表建立了索引 如何得知sql server 是否使用了该索引?
- 刚开始学习SQL遇到问题,希望大家来帮忙看看,感谢不尽。
- 反过来用IN和REPLACE函数的问题?
- sql2000 中建上全文索引后,怎样搜索中文?
- 跟两个表有关系的触发器要怎么写?在线等待
- 关于触发器的多种触发条件判断响应问题在线等待马上给分
- 包含系统存储过程/函数的数据库是哪个?
- SQL无关联多表模糊查询
DECLARE @folderNode HIERARCHYID = '/2/1/'
update dbo.Folder
set [email protected]('/2/1/','/2/1/1/') where description ='H'
GO
DECLARE @folderNode HIERARCHYID = '/1/'
, @OldParent hierarchyid = '/1/'
, @NewParent hierarchyid = '/2/1/'
UPDATE dbo.Folder
SET foldernode = @folderNode.GetReparentedValue(@OldParent, @NewParent)
WHERE description ='B'(1 行受影响)(1 行受影响)SELECT *,FolderNode.ToString() FROM dbo.Folder
--result:
FolderNode Description (无列名)
0x A /
0x6AC0 B /2/1/
0x68 E /2/
0x78 F /3/
0x5AC0 C /1/1/
0x5B40 D /1/2/
0x5B56 G /1/2/1/
0x6AD6 H /2/1/1/