SQL-如何存储和导航层次结构？
我们用来对数据库中的层次结构信息进行建模和检索的方式是什么？
解决方案
回答
在SQL数据库中表示层次结构的最佳方法是什么？一种通用的便携式技术？
让我们假设层级结构大部分是被读取的，但不是完全静态的。假设这是一棵家谱。
以下是不这样做的方法：
create table person ( person_id integer autoincrement primary key, name varchar(255) not null, dob date, mother integer, father integer );
并像这样插入数据：
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
而是将节点和关系拆分为两个表。
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 );
数据是这样创建的：
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
现在，我们可以运行不涉及将表自身重新联接起来的任意查询，如果与节点在同一行中具有异常关系，则会发生这种情况。
谁有祖父母？
select * from person where person_id in
(select descendant_id from ancestor where distance=2);
我们所有的后代：
select * from person where person_id in
(select descendant_id from ancestor
where ancestor_id=1 and distance>0);
谁是叔叔？
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 2008针对这种情况引入了新的HierarchyID数据类型。使我们可以控制行在水平和垂直方向上在"树"中的位置。
回答
Oracle：选择...以...开始...以...连接
Oracle对SELECT进行了扩展，可以轻松进行基于树的检索。也许SQL Server具有类似的扩展名？
该查询将遍历嵌套关系存储在父列和子列中的表。
select * from my_table
start with parent = :TOP
connect by prior child = parent;
http://www.adp-gmbh.ch/ora/sql/connect_by.html
回答
如果我们使用的是SQL Server 2005，则此链接说明如何检索分层数据。
只要我们习惯使用通用表表达式(CTE)，它们就可以成为朋友。
回答
我必须不同意乔希。如果我们使用的是公司组织这样的庞大层次结构，会发生什么情况。人们可以加入/离开公司，更改报告线等。维护"距离"将是一个大问题，我们将必须维护两个数据表。
此查询(SQL Server 2005及更高版本)将使我们看到任何人的完整行，并计算他们在层次结构中的位置，并且只需要一个用户信息表。可以对其进行修改以查找任何子关系。
--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;
回答
我更喜欢Josh和Mark Harrison使用的技术：
两个表，一个表包含Person的数据，另一个表包含层次结构信息(person_id，parent_id [，mother_id])(如果此表的PK为person_id)，则我们有一棵简单的树，其中每个节点只有一个父树(在在这种情况下，但在其他情况下(例如会计帐户)则没有)
可以通过递归过程或者如果数据库通过诸如SELECT ... BY PRIOR(Oracle)之类的语句来支持此层次结构表。
其他可能性是，如果我们知道要维护的层次结构数据的最大深度是使用单个表，并且每个层次结构级别具有一组列
回答
关于这一主题的确定性文章是由Joe Celko撰写的，他已经将其中的许多著作写成了一本名为《 Joe Celko的SQL for Smarties的树和层次结构》的书。
他赞成一种称为有向图的技术。可以在这里找到他在该主题上的工作介绍。
回答
我喜欢修改后的预排序树遍历算法。此技术使查询树变得非常容易。
但是，这里是有关该主题的链接的列表，这些链接是我从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/类别：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)
图形
- 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
梨:: 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
回答
当我们为[fleXive]实现树组件并使用MySQL文档中tharkun提到的嵌套集树模型方法时，我们遇到了相同的问题。
除了(以戏剧性的方式)加快速度之外，我们还使用了分散的方法，这意味着我们对顶层右边界使用了最大的Long值，这使我们能够插入和移动节点而无需重新计算所有的左和右值。左和右的值是通过将节点的范围除以3并使用内部的三分之一作为新节点的边界来计算的。
可以在这里看到一个Java代码示例。
