关于SQLServer2005的学习笔记――树形结构问题
发布时间：2020-12-12 15:26:03 所属栏目：MsSql教程 来源：网络整理
导读：??? 关于解决树形目录是每种数据库 或大多数开发人员都要面对的问题，在这一点上 Oracle 走的跟前线一些，从 9i 起便提供了 connect by 进行了支持， 10g 又增强了相关语法；在 SQLServer2005 中，强大的 CTE 功能也提供了相应的解决方案，此外提供的表函数
|
??? 关于解决树形目录是每种数据库 或大多数开发人员都要面对的问题，在这一点上 Oracle 走的跟前线一些，从 9i 起便提供了 connect by 进行了支持， 10g 又增强了相关语法；在 SQLServer2005 中，强大的 CTE 功能也提供了相应的解决方案，此外提供的表函数功能也给出了另外一种解决思路。
?
从功能上讲的话，表函数方式更为灵活一些，毕竟基于过程的结构方式更容易实现负责的业务逻辑；但递归 CTE 构造起来更为清晰一些。
?
该文起源于《 Microsoft SQL Server 2005 技术内幕： 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,'Ina',7500);
INSERT INTO Employees VALUES(4,2,'Seraph',5000);
INSERT INTO Employees VALUES(5,'Jiru',5500);
INSERT INTO Employees VALUES(6,'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,'Sean',3000);
INSERT INTO Employees VALUES(11,'Gabriel',3000);
INSERT INTO Employees VALUES(12,9,'Emilia',2000);
INSERT INTO Employees VALUES(13,'Michael',2000);
INSERT INTO Employees VALUES(14,'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,Level ? FROM Employees CONNECT BY PRIOR EmpID=MgrID ? START WITH EmpID=9 -- 获取员工的所有上级节点 SELECT EmpID,Level ? FROM Employees CONNECT BY PRIOR MgrID=EmpID ? START WITH EmpID=14 ? ? -- 构建递归 CTE ，也可以灵活获取满足不同级别的上下级节点 WITH EmployeeTree AS ( ? SELECT EmpID, ???????? 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) ? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |