[SQL2005] 通过 cross apply 和CTE返回树型记录集合方法(借用帮助文档)

--建立测试环境

--Create Employees table and insert values.
CREATE TABLE Employees
(
empid   int         NOT NULL,
mgrid   int         NULL,
empname varchar(25) NOT NULL,
salary  money       NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
)
GO
INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)
GO
--Create Departments table and insert values.
CREATE TABLE Departments
(
deptid    INT NOT NULL PRIMARY KEY,
deptname  VARCHAR(25) NOT NULL,
deptmgrid INT NULL REFERENCES Employees
)
GO
INSERT INTO Departments VALUES(1, 'HR',           2)
INSERT INTO Departments VALUES(2, 'Marketing',    7)
INSERT INTO Departments VALUES(3, 'Finance',      8)
INSERT INTO Departments VALUES(4, 'R&D',          9)
INSERT INTO Departments VALUES(5, 'Training',     4)
INSERT INTO Departments VALUES(6, 'Gardening', NULL)

Departments 表中的多数部门都具有一个经理 ID，这些 ID 与 Employees 表中的雇员相对应。以下表值函数接受雇员 ID 作为参数，并返回该雇员和他/她的所有下属。

建立函数

CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE
(
empid   INT NOT NULL,
empname VARCHAR(25) NOT NULL,
mgrid   INT NULL,
lvl     INT NOT NULL
)
AS
BEGIN
WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM employees
WHERE empid = @empid
UNION all
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM employees AS e
JOIN employees_subtree AS es
ON e.mgrid = es.empid
)
INSERT INTO @TREE
SELECT * FROM Employees_Subtree
RETURN
END
GO

若要返回每个部门经理的各级下属，请使用以下查询。

SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
注意，Departments 表中每一行复制的次数与针对部门经理执行 fn_getsubtree 而返回的行数相同。 
另外，结果中不显示 Gardening 部门。因为该部门没有经理，所以 fn_getsubtree 将针对该部门返回空集。通过使用 OUTER APPLY，Gardening 部门也将在结果集中显示，其 deptmgrid 字段以及由 fn_getsubtree 返回的字段中的值都为空值。

多个CTE一起使用

WITH   EmpOrdersCTE(EmployeeID, Cnt)
AS
(
  SELECT EmployeeID, COUNT(*) Cnt
  FROM Purchasing.PurchaseOrderHeader
  GROUP BY EmployeeID
),
MinMaxCTE(MinCnt, MaxCnt, DiffCnt)
AS
(
  SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
  FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE

原文地址

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/udb9/html/0208b259-7129-4d9f-9204-8445a8119116.htm