关于SQLServer2005的学习笔记—树形结构

关于解决树形目录是每种或大多数开发人员都要面对的问题，在这一点上Oracle走的跟前线一些，从9i起便提供了connect by进行了支持，又增强了相关语法；在SQLServer2005中，强大的CTE功能也提供了相应的解决方案，此外提供的表函数功能也给出了另外一种解决思路。

从功能上讲的话，表函数方式更为灵活一些，毕竟基于过程的结构方式更容易实现负责的业务逻辑；但递归CTE构造起来更为清晰一些。

该文起源于《Microsoft 技术内幕：T-SQL查询》，但与文中所述不尽相同。

首先构建一个标准的树形结构的员工表

CREATE TABLE Employees

(

 EmpID       INT,

 MgrID       INT,

 EmpName     VARCHAR(25),

 Salary      MONEY,

 CHECK(EmpID<>MgrID)

);

GO

INSERT INTO Employees VALUES(1,NULL,'David',10000);

INSERT INTO Employees VALUES(2,1,'Eitan',7000);

INSERT INTO Employees VALUES(3,1,'Ina',7500);

INSERT INTO Employees VALUES(4,2,'Seraph',5000);

INSERT INTO Employees VALUES(5,2,'Jiru',5500);

INSERT INTO Employees VALUES(6,2,'Steve',4500);

INSERT INTO Employees VALUES(7,3,'Aaron',5000);

INSERT INTO Employees VALUES(8,5,'Lilach',3500);

INSERT INTO Employees VALUES(9,7,'Rita',3000);

INSERT INTO Employees VALUES(10,5,'Sean',3000);

INSERT INTO Employees VALUES(11,7,'Gabriel',3000);

INSERT INTO Employees VALUES(12,9,'Emilia',2000);

INSERT INTO Employees VALUES(13,9,'Michael',2000);

INSERT INTO Employees VALUES(14,9,'Didi',1500);

 

--让我们先来看看Oracle是如何实现的吧

--获取所有相关员工信息，并构建其级别和相应的结构指向

SELECT EmpID,MgrID,EmpName,Salary,Level,sys_connect_by_path(NVL(EmpID,'0'),'->')

 FROM Employees

CONNECT BY PRIOR EmpID=MgrID

 START WITH MgrID IS NULL

--获取员工的所有下级节点

SELECT EmpID,MgrID,EmpName,Salary,Level

 FROM Employees

CONNECT BY PRIOR EmpID=MgrID

 START WITH EmpID=9

--获取员工的所有上级节点

SELECT EmpID,MgrID,EmpName,Salary,Level

 FROM Employees

CONNECT BY PRIOR MgrID=EmpID

 START WITH EmpID=14

 

--构建递归CTE，也可以灵活获取满足不同级别的上下级节点

WITH EmployeeTree

AS

(

 SELECT EmpID,MgrID,EmpName,Salary,

        0 AS Level,

        CAST(CASE WHEN MgrID IS NULL THEN 'Root' END AS VARCHAR(50)) MgrList

   FROM Employees

  WHERE MgrID IS NULL --此处亦可修改为MgrID=@Root，即传入的节点，即可得到想要的节点内容

 UNION ALL

 SELECT C.EmpID,C.MgrID,C.EmpName,C.Salary,

        P.Level+1 AS Level,

        CAST(CAST(P.MgrList AS VARCHAR(50))+'->'+CAST(C.EmpID AS VARCHAR(10)) AS VARCHAR(50)) MgrList

  FROM EmployeeTree P,Employees C

  WHERE C.MgrID=P.EmpID --AND P.Level<2设定相关级别

)

--所有员工

SELECT * FROM EmployeeTree

--求某员工上级

SELECT * FROM EmployeeTree

 WHERE CHARINDEX(MgrList,(SELECT MgrList FROM EmployeeTree WHERE EmpID=7))>0

--求某员工下级

SELECT * FROM EmployeeTree

 WHERE MgrList LIKE (SELECT MgrList FROM EmployeeTree WHERE EmpID=7)+'%'

--求某员工下级并且符合相应级数的

SELECT * FROM EmployeeTree

 WHERE MgrList LIKE (SELECT MgrList FROM EmployeeTree WHERE EmpID=7)+'%'

  AND Level<=(SELECT Level FROM EmployeeTree WHERE EmpID=7)+1

 

--通过表函数方式返回相关节点

CREATE FUNCTION fn_GetEmployeeTree(@root AS INT)

RETURNS @Subs TABLE

(

 EmpID INT,

 Level INT

)

AS

BEGIN

 DECLARE @Level AS INT;

 SET @Level=0;

 INSERT INTO @Subs(EmpID,Level) SELECT EmpID,@Level FROM Employees WHERE EmpID=@root;

 

 WHILE @@rowcount>0

 BEGIN

   SET @Level=@Level+1;

   INSERT INTO @Subs(EmpID,Level)

   SELECT C.EmpID,@Level

     FROM @SubS AS P

     JOIN Employees AS C

       ON P.Level=@Level-1

      AND C.MgrID=P.EmpID

 END

   

 RETURN;

 

END

SELECT * FROM fn_GetEmployeeTree(1)