关于sql server：SQL-如何存储和导航层次结构？

SQL - How to store and navigate hierarchies?

您用来对数据库中的层次结构信息进行建模和检索的方式是什么？

我喜欢修改的预排序树遍历算法。此技术使查询树变得非常容易。

但是，这里是有关该主题的链接列表，这些链接是我从Zend Framework(PHP)贡献者网页上复制的(由Laurent Melmoux在2007年6月5日15:52发布)。

许多链接与语言无关：

有两种主要的表示形式和算法来表示数据库的层次结构：

嵌套集也称为改进的预序树遍历算法
邻接表模型

在这里有很好的解释：

http://www.sitepoint.com/article/hierarchical-data-database
在MySQL中管理分层数据
http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/index.html

这是我收集的更多链接：

http://en.wikipedia.org/wiki/Tree_%28data_structure%29
http://en.wikipedia.org/wiki/Category:Trees_%28structure%29

邻接表模型

http://www.sqlteam.com/item.asp?ItemID=8866

嵌套集

http://www.sqlsummit.com/AdjacencyList.htm
http://www.edutech.ch/contribution/nstrees/index.php
http://www.phpriot.com/d/articles/php/application-design/nested-trees-1/
http://www.dbmsmag.com/9604d06.html
http://en.wikipedia.org/wiki/Tree_traversal
http://www.cosc.canterbury.ac.nz/mukundan/dsal/BTree.html(小程序Java montrant le fonctionnement)

Graphes

http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

课程：

嵌套集数据库树Adodb

http://www.phpclasses.org/browse/package/2547.html

访问模型ADOdb

http://www.phpclasses.org/browse/package/2919.html

PEAR :: DB_NestedSet

http://pear.php.net/package/DB_NestedSet
利用率：https://www.entwickler.com/itr/kolumnen/psecom,id,26,nodeid,207.html

梨树

http://pear.php.net/package/Tree/download/0.3.0/
http://www.phpkitchen.com/index.php?/archives/337-PEARTree-Tutorial.html

nstrees

http://www.edutech.ch/contribution/nstrees/index.php

关于这一主题的确定性文章是由Joe Celko撰写的，他已经将其中的许多著作写成一本书，名为《 SQL for Smarties中的Joe Celko的树和层次结构》。

他赞成一种称为有向图的技术。在此可以找到他在该主题上的工作简介。

在SQL数据库中表示层次结构的最佳方法是什么？一种通用的便携式技术？

假设层级结构大部分是被读取的，但不是完全静态的。假设这是一棵家谱。

不这样做的方法如下：

1
2
3
4
5
6
7

CREATE TABLE person (
person_id INTEGER autoincrement PRIMARY KEY,
name VARCHAR(255) NOT NULL,
dob DATE,
mother INTEGER,
father INTEGER
);

并像这样插入数据：

1
2
3
4
5
6
7

person_id name dob mother father
1 Pops 1900/1/1 NULL NULL
2 Grandma 1903/2/4 NULL NULL
3 Dad 1925/4/2 2 1
4 Uncle Kev 1927/3/3 2 1
5 Cuz Dave 1953/7/8 NULL 4
6 Billy 1954/8/1 NULL 3

而是将节点和关系拆分为两个表。

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE person (
person_id INTEGER autoincrement PRIMARY KEY,
name VARCHAR(255) NOT NULL,
dob DATE
);

CREATE TABLE ancestor (
ancestor_id INTEGER,
descendant_id INTEGER,
distance INTEGER
);

数据创建如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

person_id name dob
1 Pops 1900/1/1
2 Grandma 1903/2/4
3 Dad 1925/4/2
4 Uncle Kev 1927/3/3
5 Cuz Dave 1953/7/8
6 Billy 1954/8/1

ancestor_id descendant_id distance
1 1 0
2 2 0
3 3 0
4 4 0
5 5 0
6 6 0
1 3 1
2 3 1
1 4 1
2 4 1
1 5 2
2 5 2
4 5 1
1 6 2
2 6 2
3 6 1

现在，您可以运行不涉及将表自身重新连接起来的任意查询，如果与节点在同一行中具有异常关系，则会发生这种情况。

谁有祖父母？

1 2	SELECT * FROM person WHERE person_id IN (SELECT descendant_id FROM ancestor WHERE distance=2);

您所有的后代：

1
2
3

SELECT * FROM person WHERE person_id IN
(SELECT descendant_id FROM ancestor
WHERE ancestor_id=1 AND distance>0);

谁是叔叔？

1
2
3
4
5
6
7

SELECT decendant_id uncle FROM ancestor
WHERE distance=1 AND ancestor_id IN
(SELECT ancestor_id FROM ancestor
WHERE distance=2 AND NOT EXISTS
(SELECT ancestor_id FROM ancestor
WHERE distance=1 AND ancestor_id=uncle)
)

您避免了通过子查询将表连接到自身的所有问题，常见的限制是16个子查询。

麻烦的是，维护祖先表有点困难-最好通过存储过程来完成。

我必须不同意乔希。如果您使用的是公司组织这样的大型分层结构，会发生什么。人们可以加入/离开公司，更改报告线，等等。维护"距离"将是一个大问题，您将必须维护两个数据表。

该查询(SQL Server 2005及更高版本)将使您看到任何人的完整行，并计算他们在层次结构中的位置，并且只需要一个用户信息表。可以对其进行修改以查找任何子关系。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

--Create table of dummy data
CREATE TABLE #person (
personID INTEGER IDENTITY(1,1) NOT NULL,
name VARCHAR(255) NOT NULL,
dob DATE,
father INTEGER
);

INSERT INTO #person(name,dob,father)VALUES('Pops','1900/1/1',NULL);
INSERT INTO #person(name,dob,father)VALUES('Grandma','1903/2/4',NULL);
INSERT INTO #person(name,dob,father)VALUES('Dad','1925/4/2',1);
INSERT INTO #person(name,dob,father)VALUES('Uncle Kev','1927/3/3',1);
INSERT INTO #person(name,dob,father)VALUES('Cuz Dave','1953/7/8',4);
INSERT INTO #person(name,dob,father)VALUES('Billy','1954/8/1',3);

DECLARE @OldestPerson INT;
SET @OldestPerson = 1; -- Set this value to the ID of the oldest person in the family

WITH PersonHierarchy (personID,Name,dob,father, HierarchyLevel) AS
(
SELECT
personID
,Name
,dob
,father,
1 AS HierarchyLevel
FROM #person
WHERE personID = @OldestPerson

UNION ALL

SELECT
e.personID,
e.Name,
e.dob,
e.father,
eh.HierarchyLevel + 1 AS HierarchyLevel
FROM #person e
INNER JOIN PersonHierarchy eh ON
e.father = eh.personID
)

SELECT *
FROM PersonHierarchy
ORDER BY HierarchyLevel, father;

DROP TABLE #person;

仅供参考：SQL Server 2008针对这种情况引入了新的HierarchyID数据类型。使您可以控制行在水平和垂直方向上在"树"中的位置。

Oracle：选择...以...开始...以...连接

Oracle对SELECT进行了扩展，可以轻松进行基于树的检索。也许SQL Server具有类似的扩展名？

该查询将遍历嵌套关系存储在父列和子列中的表。

1
2
3

SELECT * FROM my_table
START WITH parent = :TOP
CONNECT BY prior child = parent;

http://www.adp-gmbh.ch/ora/sql/connect_by.html

我更喜欢Josh和Mark Harrison所用的技术：

两张表，其中一张包含Person的数据，另一张带有层次结构信息(person_id，parent_id [，mother_id])(如果此表的PK为person_id)，则您有一棵简单的树，其中每个节点只有一个父级(在在这种情况下，但在其他情况下(例如会计帐户)则没有)

可以通过递归过程或如果您的数据库通过诸如SELECT ... BY PRIOR(Oracle)之类的语句支持该层次结构表。

其他可能性是，如果您知道要维护的层次结构数据的最大深度是使用单个表，并且每个层次结构级别具有一组列

当为[fleXive]实现树组件并使用MySQL文档中tharkun提到的嵌套集树模型方法时，我们遇到了相同的问题。