当前位置：文江博客话题详情

SQL - 如何存储和导航层次结构？

发布于 2024-07-05 12:42:22 字数 30 浏览 5 评论 0原文

您使用哪些方法来建模和检索数据库中的分层信息？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

兲鉂ぱ嘚淚 2024-07-12 12:42:23

我不同意乔希的观点。如果您使用像公司组织这样的巨大层次结构，会发生什么情况。人们可以加入/离开公司，改变汇报关系等等……保持“距离”将是一个大问题，你必须维护两个数据表。

此查询（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;

I've got to disagree with Josh. What happens if you're using a huge hierarchical structure like a company organization. People can join/leave the company, change reporting lines, etc... Maintaining the "distance" would be a big problem and you would have to maintain two tables of data.

This query (SQL Server 2005 and above) would let you see the complete line of any person AND calculates their place in the hierarchy and it only requires a single table of user information. It can be modified to find any child relationship.

--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;

回复收藏 0 原文

情徒 2024-07-12 12:42:23

仅供参考：SQL Server 2008 为此排序引入了新的 HierarchyID 数据类型的情况。让您可以控制行在“树”中的水平和垂直位置。

回复收藏 0 原文

落叶缤纷 2024-07-12 12:42:23

Oracle：SELECT ... START WITH ... CONNECT BY

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

Oracle: SELECT ... START WITH ... CONNECT BY

Oracle has an extension to SELECT that allows easy tree-based retrieval. Perhaps SQL Server has some similar extension?

This query will traverse a table where the nesting relationship is stored in parent and child columns.

select * from my_table
    start with parent = :TOP
    connect by prior child = parent;

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

回复收藏 0 原文

ま昔日黯然 2024-07-12 12:42:23

我更喜欢 Josh 和 Mark Harrison 使用的技术的混合：

两个表，一个包含 Person 的数据，另一个包含层次结构信息（person_id，parent_id [， mother_id]）如果该表的 PK 是 person_id，则您有一棵只有一个父节点的简单树（在这种情况下有意义，但在会计帐户等其他情况下则不然）。

这个层次结构表可以通过递归过程来遍历，或者如果您的数据库通过诸如 SELECT...BY PRIOR 之类的语句支持它（甲骨文）。

另一种可能性是，如果您知道要维护的层次结构数据的最大深度，则使用单个表，每个层次结构级别包含一组列

回复收藏 0 原文

[旋木] 2024-07-12 12:42:23

当我们为 [fleXive] 实现树组件时，我们遇到了同样的问题使用 tharkun 在 MySQL 文档中。

除了（显着）加快速度之外，我们还使用了一种扩展方法，这意味着我们使用了顶级右边界的最大 Long 值，这允许我们插入和移动节点，而无需重新计算所有左和右价值观。左和右的值是通过将节点的范围除以 3 并使用内部三分之一作为新节点的边界来计算的。

java代码示例可以参见此处。

回复收藏 0 原文

So尛奶瓶 2024-07-12 12:42:23

如果您使用的是 SQL Server 2005，则此链接解释了如何检索分层数据。

一旦您习惯使用通用表表达式 (CTE)，它们就会成为您的朋友。

回复收藏 0 原文

波浪屿的海角声 2024-07-12 12:42:22

我喜欢改进的先序树遍历算法。这种技术使得查询树变得非常容易。

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

许多链接与语言无关：

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

嵌套集也称为修改的先序树遍历算法
邻接列表模型

这里有很好的解释：

以下是我收集的更多链接：

邻接列表模型

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

嵌套集

图表

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

类：

嵌套集 DB 树 Adodb

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

访问模型 ADOdb

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

PEAR::DB_NestedSet

PEAR::Tree

nstrees

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

回复收藏 0 原文

初心 2024-07-12 12:42:22

关于这个主题的权威文章由 Joe Celko 撰写，他将其中的一些文章整理成一本名为《Joe Celko's Trees and Hierarchies in SQL for Smarties》的书。

他喜欢一种称为有向图的技术。有关他在该主题上的工作的介绍，请参见这里

回复收藏 0 原文

甜是你 2024-07-12 12:42:22

在 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 个子查询。

问题是，维护祖先表有点困难 - 最好使用存储过程来完成。

What's the best way to represent a hierachy in a SQL database? A generic, portable technique?

Let's assume the hierachy is mostly read, but isn't completely static. Let's say it's a family tree.

Here's how not to do it:

create table person (
person_id integer autoincrement primary key,
name      varchar(255) not null,
dob       date,
mother    integer,
father    integer
);

And inserting data like this:

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

Instead, split your nodes and your relationships into two tables.

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
);

Data is created like this:

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

you can now run arbitary queries that don't involve joining the table back on itself, which would happen if you have the heirachy relationship in the same row as the node.

Who has grandparents?

select * from person where person_id in 
    (select descendant_id from ancestor where distance=2);

All your descendants:

select * from person where person_id in 
    (select descendant_id from ancestor 
    where ancestor_id=1 and distance>0);

Who are uncles?

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)
    )

You avoid all the problems of joining a table to itself via subqueries, a common limitation is 16 subsuqeries.

Trouble is, maintaining the ancestor table is kind of hard - best done with a stored procedure.

回复收藏 0 原文

~没有更多了~