SQL Server 2005 Beta 2 Transact-SQL 增强功能(一)

锚定成员返回一个含有每个雇员的经理 ID 的行。经理 ID 列中的 NULL 被排除，因为它不代表特定的经理。递归成员返回先前返回的经理的经理的经理 ID，NULL 再次被排除。最后，CTE 为每个经理包含像它们的直接或间接下属数量一样多的实例。外部查询负责完成按经理 ID 对结果进行分组以及返回实例计数的任务。

作为针对单父节点层次结构的请求的另一个示例，假设您希望返回 Nancy 的按照层次依赖项排序和缩进的下属。以下代码恰好完成该任务，它按照同辈的雇员 ID 对它们进行排序：

WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,
CAST(empid AS VARBINARY(900))
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE('' | '', lvl)
+ ''('' + (CAST(empid AS VARCHAR(10))) + '') ''
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
(1) Nancy
| (2) Andrew
| | (5) Steven
| | (6) Michael
| (3) Janet
| | (7) Robert
| | | (11) David
| | | | (14) James
| | | (12) Ron
| | | (13) Dan
| | (8) Laura
| | (9) Ann

| (4) Margaret
| | (10) Ina

要按照 empid 值对同辈进行排序，请为每个雇员构建一个名为 sortcol 的二进制字符串。该字符串由通向每个雇员的管理链中串联在一起的雇员 ID 组成（转换为二进制值）。锚定成员是起始点。它用根雇员的 empid 生成一个二进制值。在每个迭代中，递归成员都将被转换为二进制值的当前雇员 ID 追加到经理的 sortcol 中。然后，外部查询按照 sortcol 对结果进行排序。请记住，锚定成员和递归成员中的对应列必须具有相同的数据类型、长度和精度。这就是生成 sortcol 值的表达式被转换为 varbinary(900) 的原因（即使整数的二进制表示需要 4 个字节）：900 个字节覆盖 225 个级别（这似乎不是一个合理的限制）。如果您希望支持更多的级别，则可以增加该长度，但是，请确保在这两个成员中执行该操作;否则，您将获得错误。

层次缩进是通过将字符串（在该示例中为 '' | ''）复制与雇员的级别数一样多的次数实现的。为此，需要在括号中追加雇员 ID 本身，并且最后还追加雇员名字。

可以使用类似的技术，按照其他可以转换为较小的定长二进制值的属性（例如，smalldatetime 列中存储的雇员雇用日期）对同辈进行排序。如果您希望按照不可转换为较小的定长二进制值的属性（例如，雇员名字）对同辈进行排序，则可以首先产生按照表示所需排序的经理 ID 分段的整数行号（有关行号的详细信息，请参阅前文中的“排序函数”一节），如下所示：

SELECT empid, empname, mgrid,
ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname) AS pos
FROM Employees

并且，请串联被转换为二进制值的雇员位置，而不是串联被转换为二进制值的雇员 ID：

WITH EmpPos(empid, empname, mgrid, pos)
AS
(
SELECT empid, empname, mgrid,
ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname) AS pos
FROM Employees
),
EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,

CAST(pos AS VARBINARY(900))
FROM EmpPos
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.pos AS BINARY(4)) AS VARBINARY(900))
FROM EmpPos AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE('' | '', lvl)
+ ''('' + (CAST(empid AS VARCHAR(10))) + '') ''
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
(1) Nancy
| (2) Andrew
| | (6) Mich