mysql hierarchyid_SQL Server-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个子查询。

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