SQL Server 2005 Beta 2 Transact-SQL 增强功能 1

核心提示： 您还可以使用递归查询来计算聚合，例如，SQL Server 2005 Beta 2 Transact-SQL 增强功能 1(9)，直接或间接向每个经理汇报的下属的数量：WITH MgrCTE(mgrid, lvl)AS(SELECT mgrid, 0FROM EmployeesWHERE mgrid IS NOT N

您还可以使用递归查询来计算聚合，例如，直接或间接向每个经理汇报的下属的数量：

WITH MgrCTE(mgrid, lvl)AS(SELECT mgrid, 0FROM EmployeesWHERE mgrid IS NOT NULLUNION ALLSELECT M.mgrid, lvl + 1FROM Employees AS MJOIN MgrCTE AS EON E.mgrid = M.empidWHERE M.mgrid IS NOT NULL)SELECT mgrid, COUNT(*) AS cntFROM MgrCTEGROUP BY mgrid

以下为结果集：

mgrid       cnt----------- -----------1           132           23           74           17           411          1

锚定成员返回一个含有每个雇员的经理 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 EmployeesWHERE empid = 1UNION ALLSELECT E.empid, E.empname, E.mgrid, M.lvl+1,CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))FROM Employees AS EJOIN EmpCTE AS MON E.mgrid = M.empid)SELECTREPLICATE(' | ', lvl)+ '(' + (CAST(empid AS VARCHAR(10))) + ') '+ empname AS empnameFROM EmpCTEORDER 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 posFROM Employees

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

WITH EmpPos(empid, empname, mgrid, pos)AS(SELECT empid, empname, mgrid,ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname) AS posFROM Employees),EmpCTE(empid, empname, mgrid, lvl, sortcol)AS(SELECT empid, empname, mgrid, 0,CAST(pos AS VARBINARY(900))FROM EmpPosWHERE empid = 1UNION ALLSELECT E.empid, E.empname, E.mgrid, M.lvl+1,CAST(sortcol + CAST(E.pos AS BINARY(4)) AS VARBINARY(900))FROM EmpPos AS EJOIN EmpCTE AS MON E.mgrid = M.empid)SELECTREPLICATE(' | ', lvl)+ '(' + (CAST(empid AS VARCHAR(10))) + ') '+ empname AS empnameFROM EmpCTEORDER BY sortcol(1) Nancy| (2) Andrew|  | (6) Michael|  | (5) Steven| (3) Janet|  | (9) Ann|  | (8) Laura|  | (7) Robert|  |  | (13) Dan|  |  | (11) David|  |  |  | (14) James|  |  | (12) Ron| (4) Margaret|  | (10) Ina

要按照其他任何属性或属性组合对同辈进行排序，只须在 ROW_NUMBER 函数的 OVER 选项的 ORDER BY 列表中指定所需的属性，而不是指定 empname。