树形结构数据库设计

1 概述
树形数据，主要关注的是：
1> 如何将数据高效地以树形的形式展现给用户
2> 通过某个节点找到所有的父节点。
3> 获取某个节点的所有的后继节点（包括子节点的子节点）
至于添加、修改、删除和通过一个父节点获取对应的子节点，都是可以很容易的实现。

2 邻接模型
2.1业务：文件存放位置，在档案管理中，需要为文件的存放位置建模，文件存在抽屉，然后抽屉在某个柜子中，柜子在某个房间中。
2.2表结构：

2.3备注
可以在表中再加入一个level_num字段(表示所处在树的深度)，这样就少了那一个递归查询的操作，但是在管理上有做一些处理。

2.4 测试数据

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location]'))DROP TABLE [dbo].LocationGO--位置表CREATE TABLE dbo.Location(    id int,    name nvarchar(50),    parent_id int,    order_no int);INSERT INTO dbo.LocationSELECT 1,'房间1',0,1UNION ALLSELECT 2,'柜子11',1,2UNION ALLSELECT 3,'抽屉111',2,3UNION ALLSELECT 4,'抽屉122',2,4UNION ALLSELECT 5,'柜子12',1,5UNION ALLSELECT 6,'抽屉121',5,6UNION ALLSELECT 7,'房间2',0,7UNION ALLSELECT 8,'柜子21',7,8UNION ALLSELECT 9,'柜子22',7,9UNION ALLSELECT 10,'房间3',0,10

2.5 如何将数据高效地以树形的形式展现给用户，执行SQL：

;WITH locationT AS(    SELECT L.id,L.name,L.parent_id,L.order_no,0 AS levelL    FROM Location AS L     WHERE L.parent_id=0    UNION ALL    SELECT LC.id,LC.name,LC.parent_id,LC.order_no,LP.levelL+1    FROM Location AS LC        INNER JOIN locationT AS LP ON LC.parent_id=LP.id)SELECT * ,    CASE levelL    WHEN 0 THEN '|-'+name    WHEN 1 THEN '|-|-'+name    WHEN 2 THEN '|-|-|-'+name    WHEN 3 THEN '|-|-|-|-'+name    END AS level_nameFROM locationTORDER BY order_no

结果：

备注：其中CASE levelL WHEN 0 THEN '|-'+name WHEN 1 THEN '|-|-'+name WHEN 2 THEN '|-|-|-'+name WHEN 3 THEN '|-|-|-|-'+name END AS level_name 为树的深度大概可知，不会很深时，可以这样做，但是如果树的深度不可知，可以用下面的SQL,不过效率会低一些：

 

SELECT * ,replace(replace(str(0,(levelL+1)),' ','0'),'0','|-')+name level_nameFROM locationTORDER BY order_no

 

2.6 通过某个节点找到所有的父节点,执行SQL：

;WITH locationT(id,name_level,name,parent_id,order_no,levelL) AS(    SELECT L.id,CAST(L.name AS NVARCHAR(1000)) AS name_level,L.name,L.parent_id,L.order_no,0 AS levelL    FROM Location AS L     WHERE L.parent_id=0    UNION ALL    SELECT LC.id,CAST(LP.name_level+','+LC.name AS NVARCHAR(1000)) AS name_level,LC.name,LC.parent_id,LC.order_no,LP.levelL+1    FROM Location AS LC        INNER JOIN locationT AS LP ON LC.parent_id=LP.id)SELECT * FROM locationTWHERE name='抽屉111'ORDER BY order_no

结果：

2.7 获取某个节点的所有子节点

;WITH locationT AS(    SELECT L.id,L.name,L.parent_id,L.order_no,0 AS levelL    FROM Location AS L     WHERE L.name='房间1'    UNION ALL    SELECT LC.id,LC.name,LC.parent_id,LC.order_no,LP.levelL+1    FROM Location AS LC        INNER JOIN locationT AS LP ON LC.parent_id=LP.id)SELECT * ,    CASE levelL    WHEN 0 THEN '|-'+name    WHEN 1 THEN '|-|-'+name    WHEN 2 THEN '|-|-|-'+name    WHEN 3 THEN '|-|-|-|-'+name    END AS level_nameFROM locationTORDER BY order_no

结果：

3 物化路径模型
3.1业务：文件存放位置，在档案管理中，需要为文件的存放位置建模，文件存在抽屉，然后抽屉在某个柜子中，柜子在某个房间中。
3.2表结构：

3.3备注：

此时不加入parent_id也可以完成操作，但是加入parent_id可以简化管理的操作，Level_num也可以不加入，但是加入后可以在显示数据时简化操作。

3.4测试数据：

View Code

2.5 如何将数据高效地以树形的形式展现给用户，执行SQL：

SELECT * ,    CASE level_num    WHEN 0 THEN '|-'+name    WHEN 1 THEN '|-|-'+name    WHEN 2 THEN '|-|-|-'+name    WHEN 3 THEN '|-|-|-|-'+name    END AS level_nameFROM Location2 AS LORDER BY level_code

结果：

2.6 通过某个节点找到所有的父节点,执行SQL：

DECLARE @level_code varchar(1000)='001.001.001';;WITH locationRowT AS(    SELECT TOP 100 PERCENT * ,ROW_NUMBER()OVER(ORDER BY id) AS row_id    FROM Location2 AS L    ORDER BY level_code)SELECT * FROM locationRowT AS LT    CROSS JOIN(SELECT row_id AS start_index FROM locationRowT WHERE level_code=SUBSTRING(@level_code,1,3)) AS LTR    CROSS JOIN(SELECT row_id AS end_index FROM locationRowT WHERE level_code=@level_code) AS LTCWHERE LT.row_id BETWEEN LTR.start_index AND LTC.end_index

结果：

2.7 获取某个节点的所有子节点

SELECT * FROM Location2 AS LWHERE level_code LIKE '001%'ORDER BY level_code

结果：