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

1 概述

树形数据，主要关注的是：

1> 如何将数据高效地以树形的形式展现给用户

2> 通过某个节点找到所有的父节点。

3> 获取某个节点的所有的后继节点(包括子节点的子节点)

至于添加、修改、删除和通过一个父节点获取对应的子节点，都是可以很容易的实现。

2 邻接模型

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

2.2表结构：

2.3备注

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

2.4测试数据

View Code

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location]'))DROP TABLE [dbo].LocationGO

--位置表

CREATE TABLEdbo.Location

(

idint,

namenvarchar(50),

parent_idint,

order_noint);INSERT INTOdbo.LocationSELECT 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 ASlevelLFROM Location ASLWHERE L.parent_id=0

UNION ALL

SELECT LC.id,LC.name,LC.parent_id,LC.order_no,LP.levelL+1

FROM Location ASLCINNER JOIN locationT AS LP ON LC.parent_id=LP.id

)SELECT *,CASElevelLWHEN 0 THEN '|-'+nameWHEN 1 THEN '|-|-'+nameWHEN 2 THEN '|-|-|-'+nameWHEN 3 THEN '|-|-|-|-'+nameEND ASlevel_nameFROMlocationTORDER BY order_no

结果：

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

SELECT * ,replace(replace(str(0,(levelL+1)),' ','0'),'0','|-')+name level_nameFROMlocationTORDER 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 ASlevelLFROM Location ASLWHERE 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 ASLCINNER JOIN locationT AS LP ON LC.parent_id=LP.id

)SELECT *

FROMlocationTWHERE name='抽屉111'

ORDER BY order_no

结果：

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

;WITH locationT AS(SELECT L.id,L.name,L.parent_id,L.order_no,0 ASlevelLFROM Location ASLWHERE L.name='房间1'

UNION ALL

SELECT LC.id,LC.name,LC.parent_id,LC.order_no,LP.levelL+1

FROM Location ASLCINNER JOIN locationT AS LP ON LC.parent_id=LP.id

)SELECT *,CASElevelLWHEN 0 THEN '|-'+nameWHEN 1 THEN '|-|-'+nameWHEN 2 THEN '|-|-|-'+nameWHEN 3 THEN '|-|-|-|-'+nameEND ASlevel_nameFROMlocationTORDER BY order_no

结果：

3 物化路径模型

3.1业务：文件存放位置，在档案管理中，需要为文件的存放位置建模，文件存在抽屉，然后抽屉在某个柜子中，柜子在某个房间中。

3.2表结构：

3.3备注：

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

3.4测试数据：

View Code

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location2]'))DROP TABLE [dbo].Location2GO

--位置表

CREATE TABLEdbo.Location2

(

idint,

namenvarchar(50),

level_codevarchar(1000),

parent_idint,

level_numint);INSERT INTOdbo.Location2SELECT 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 *,CASElevel_numWHEN 0 THEN '|-'+nameWHEN 1 THEN '|-|-'+nameWHEN 2 THEN '|-|-|-'+nameWHEN 3 THEN '|-|-|-|-'+nameEND ASlevel_nameFROM Location2 ASLORDER 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) ASrow_idFROM Location2 ASLORDER BYlevel_code

)SELECT *

FROM locationRowT ASLTCROSS JOIN(SELECT row_id AS start_index FROM locationRowT WHERE level_code=SUBSTRING(@level_code,1,3)) ASLTRCROSS JOIN(SELECT row_id AS end_index FROM locationRowT WHERE level_code=@level_code) ASLTCWHERE LT.row_id BETWEEN LTR.start_index AND LTC.end_index

结果：

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

SELECT *

FROM Location2 ASLWHERE level_code LIKE '001%'

ORDER BY level_code

结果：