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

    如果需要设计一个员工的实体（employee），员工有上下级关系，而一个员工的上级也是一个员工，这就是一个自关联关系。假设这个员工数据库表设计成这样：

    Employee

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

emp_id

emp_name

manager_id

其中manager_id是管理者的员工的emp_id，由于企业机构层级可能有很多层。因此，整个数据下来，就是一棵树型结果。一般设计这种我们采取简单树，就是上面的方式，那么这样设计有什么问题呢？

1.查询问题

    假设我们查询员工id为1的员工下属有哪些？我们可以这样写SQL

SELECT T1.*,T2.*
FROM EMPLOYEE T1 LEFT JOIN EMPLOYEE T2 ON T1.EMP_ID=T2.MANAGER_ID
WHERE T1.EMP_ID =1

 但是上述查询存在一个问题，就是只能包含2个层级的数据，如果有更深层数据无法查询出来，因此数据不正确；

2.递归查询

   有些人可能想到递归查询，这是一种方法，但是递归方法语法复杂，而且有些数据库是不支持递归查询的。

解决方案1：使用存储全路径的列

修改实体为如下方式：

Employee

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

emp_id

emp_name

path

查询员工id为1的员工下属有哪些？这时候写SQL就非常简单了,也不需要递归查询。如下：

SELECT * FROM EMPLOYEE 
WHERE PATH LIKE '1%'

解决方案2：嵌套集合（nested sets）

此方案设计实体如下

Employee

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

emp_id

emp_name

nsleft

nsright

此时增加了一个nsleft,nsright，这两个值是根据树的结果，使用深度优先遍历确定的一个节点的左值和右值。

那么此时需要查询应该是什么样的呢？

SELECT T2.*
FROM EMPLOYEE T1 
JOIN EMPLOYEE T2
ON T2.NSLEFT BETWEEN T1.NSLEFT AND T1.NSRIGHT
WHERE T1.EMP_ID=1

根据树的原理，这样的查询就可以得到所有的下级。当然这种方法也有缺点，就是维护整个树比较麻烦，要是插入修改和删除节点都需要保持整个树的nsleft,nsright的正确性。比较适合哪些不经常变化的数据。