常见数据库设计（4）——树形结构数据

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].Location
GO
--位置表
CREATE TABLE dbo.Location
(
    id int,
    name nvarchar(50),
    parent_id int,
    order_no int
);

INSERT INTO dbo.Location
SELECT 1,'房间1',0,1
UNION ALL
SELECT 2,'柜子11',1,2
UNION ALL
SELECT 3,'抽屉111',2,3
UNION ALL
SELECT 4,'抽屉122',2,4
UNION ALL
SELECT 5,'柜子12',1,5
UNION ALL
SELECT 6,'抽屉121',5,6
UNION ALL
SELECT 7,'房间2',0,7
UNION ALL
SELECT 8,'柜子21',7,8
UNION ALL
SELECT 9,'柜子22',7,9
UNION ALL
SELECT 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_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测试数据：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location2]'))
DROP TABLE [dbo].Location2
GO
--位置表
CREATE TABLE dbo.Location2
(
    id int,
    name nvarchar(50),
    level_code varchar(1000),
    parent_id int,
    level_num int
);

INSERT INTO dbo.Location2
SELECT 1,'房间','001',0,1
UNION ALL
SELECT 2,'柜子','001.001',1,2
UNION ALL
SELECT 3,'抽屉','001.001.001',2,3
UNION ALL
SELECT 4,'抽屉','001.001.002',2,3
UNION ALL
SELECT 5,'柜子','001.002',1,2
UNION ALL
SELECT 6,'抽屉','001.002.001',5,3
UNION ALL
SELECT 7,'房间','002',0,1
UNION ALL
SELECT 8,'柜子','002.001',7,2
UNION ALL
SELECT 9,'柜子','002.002',7,2
UNION ALL
SELECT 10,'房间','003',0,1

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

结果：