Common table expressions(CTE):
CTE is a temporary named result set or view, and a CTE can reference itself, in which case it's called a recursive query. In other words, the CTE allows you to execute recursive (hierarchical) queries in a really easy way.
以前我们都是自己写很多的 T_SQL 代码来实现这样的关系图。在 "Yukon" 有了 CTE ，问题就好解决多了。 在Sql Server 2000里，我是通过操作临时表来实现这样的功能的，不知道还有没有别的方法来实现。
看看在'Yukon'中的代码吧:
| 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 | |  Michael| (3) Janet | | (7) Robert | | | (11) David | | | | (14) James | | | (12) Ron | | | (13) Dan | |  Laura| | (9) Ann | (4) Margaret | | (10) Ina |
-
详细信息 MSDN ：SQL Server "Yukon" Beta 1 Transact-SQL Enhancements
-
关于 T_SQL 递归实现 ：Recursion in T–SQL
随贴广告(测试期)
打印 | 张贴于 2003-11-03 16:14:00 | Tag:Sql Server
留言反馈