转载——常见数据库设计——树形结构数据

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

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

 

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

2.4 测试数据

View Code

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_name
FROM locationT
ORDER 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_name
FROM locationT
ORDER 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 locationT
WHERE 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_name
FROM locationT
ORDER 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_name
FROM Location2 AS L
ORDER 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 LTC
WHERE LT.row_id BETWEEN LTR.start_index AND LTC.end_index

结果：

 

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

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

结果：