SQL反模式-简单树问题（二）

      上一篇文章介绍，简单树的结构不能使用上级Id来存储，会带来查询等问题，并提供了2个解决方案。分别是使用存储全路径的列和嵌套集合（nested sets）

解决方案：闭包表（Closure Table）

      这种方案是简单且优雅的，那么具体方案是什么呢？它需要额外建一个表，比如TreePaths表，这个表的结构是这样的

Employee

------------------

emp_id

emp_name

TreePaths

-------------

ancestor（祖先），关联emp_id

descendant（后代)，关联emp_id

这两列的结构与emp_id一致，并且是emp_id的外键，假设employee表的各个节点层级如下，上面表示管理者

其中A（id为1）是B、C的管理者，C管理D、E

那么这个层级关系如何存储到TreePaths中呢？

ancestor	descendant
1	1
1	2
1	3
1	4
1	5
2	2
3	3
3	4
3	5
4	4
5	5

 

以上数据表格存储了所有节点的层级关系，有了以上关系之后，那么想找到id为3的下属有哪些，可以使用如下SQL

select e.* from Employee e 
inner join TreePaths t on e.emp_id =t.descendant
where t.ancestor =3;

如果查询id为5的上级有哪些，可以使用如下SQL

select e.* from Employee e 
inner join TreePaths t on e.emp_id =t.ancestor
where t.descendant =5;

假设需要删除id为3的节点和子树，则使用如下SQL就可以了。当然可以仅删除TreePaths，而不是删除Employee。

delete Employee where emp_id 
in (SELECT descendant from TreePaths where ancestor =3);

delete TreePaths where descendant in
(SELECT descendant from TreePaths where ancestor =3);

上面的TreePaths可以增加一些列用于跟好的查询，比如增加path_length，这样可以去到不同层级的管理者或者下属信息，有兴趣可以自己研究一下。

最后附上SQL，供大家使用

create table Employee
(emp_id int,
emp_name nvarchar(50));
insert into Employee values
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'E');

CREATE TABLE TreePaths
(ancestor int,
descendant int);

insert into TreePaths values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(2,2),
(3,3),
(3,4),
(3,5),
(4,4),
(5,5)

select * from TreePaths;

select e.* from Employee e 
inner join TreePaths t on e.emp_id =t.descendant
where t.ancestor =3;

select e.* from Employee e 
inner join TreePaths t on e.emp_id =t.ancestor
where t.descendant =5;

delete Employee where emp_id 
in (SELECT descendant from TreePaths where ancestor =3);

delete TreePaths where descendant in
(SELECT descendant from TreePaths where ancestor =3);