注册
 找回密码
 注册
江西广告网
查看: 255|回复: 0
打印 上一主题 下一主题

SQL Server 2005 Beta 2 Transact-SQL功能(2)

[复制链接]

该用户从未签到

1
跳转到指定楼层
发表于 2009-1-15 08:32:10 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?注册

x
  单父节点环境:雇员组织结构图      对于单父节点层次结构方案,使用雇员组织结构图。      注 本节中的示例使用一个名为 Employees 的表,该表具有与 AdventureWorks 中的 HumanResources.Employee 表不同的结构。您应当在自己的测试数据库或 tempdb 中运行代码,而不要在 AdventureWorks 中运行代码。      以下代码生成 Employees 表并且用示例数据填充它:      USE tempdb -- or your own test database   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),    CONSTRAINT FK_Employees_mgrid_empid   FOREIGN KEY(mgrid)   REFERENCES Employees(empid)   )   CREATE INDEX idx_nci_mgrid ON Employees(mgrid)   SET NOCOUNT ON   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)      每个雇员都向其 ID 存储在 mgrid 列中的经理汇报。在引用 empid 列的 mgrid 列上定义了一个外键,这意味着经理 ID 必须对应于该表中的一个有效雇员 ID 或者为 NULL。老板 Nancy 在 mgrid 列中具有 NULL 值。经理-雇员关系如图 1 所示。       图 1. 雇员组织结构图      下面是一些可能在 Employees 表上运行的常见请求:      显示有关 Robert (empid=7) 及其所有级别下属的详细信息。      显示有关比 Janet (empid=3) 低两个级别的所有雇员的详细信息。      显示通向 James (empid=14) 的管理链。      显示有多少个雇员直接或间接向每个经理汇报。      以适当的方式显示所有雇员,以便可以容易地查看他们的层次依赖项。      递归 CTE 提供了处理上述请求(它们在本质上是递归的)的手段,而无须在数据库中维护有关层次结构的其他信息。      第一个请求可能是最常见的一个请求:返回某个雇员(例如,empid=7 的 Robert)及其所有级别的下属。以下 CTE 提供了对该请求的解决方案:      WITH EmpCTE(empid, empname, mgrid, lvl)   AS   (       -- Anchor Member (AM)    SELECT empid, empname, mgrid, 0    FROM Employees    WHERE empid = 7    UNION ALL       -- Recursive Member (RM)    SELECT E.empid, E.empname, E.mgrid, M.lvl 1    FROM Employees AS E   JOIN EmpCTE AS M    ON E.mgrid = M.empid   )   SELECT * FROM EmpCTE      以下为结果集:      empid    empname          mgrid    lvl   ----------- ------------------------- ----------- -----------   7      Robert          3      0   11     David           7      1   12     Ron            7      1   13     Dan            7      1   14     James           11     2      按照先前描述的递归 CTE 逻辑,该 CTE 被按如下方式处理:      锚定成员被激活,并且从 Employees 表中返回 Robert 的行。请注意在 lvl 结果列中返回的常量 0。      1. 递归成员被反复激活,并且借助于 Employees 和 EmpCTE 之间的联接操作返回上一个结果的直接下属。Employees 代表下属,而 EmpCTE(它包含上一个调用的结果)代表经理:      首先,Robert 的下属被返回:David、Ron 和 James。      然后,David、Ron 和 Dan 的下属被返回:只有 James。      最后,James 的下属被返回:无 — 在这种情况下,递归终止。      2. 外部查询从 EmpCTE 中返回所有行。      请注意,对于每个递归调用,lvl 值反复递增。      使用该级别计数器,您可以限制递归中的迭代次数。例如,以下 CTE 用来返回比 Janet 低两个级别的所有雇员:      WITH EmpCTEJanet(empid, empname, mgrid, lvl)   AS   (    SELECT empid, empname, mgrid, 0    FROM Employees    WHERE empid = 3    UNION ALL       SELECT E.empid, E.empname, E.mgrid, M.lvl 1    FROM Employees as E   JOIN EmpCTEJanet as M    ON E.mgrid = M.empid    WHERE lvl < 2   )   SELECT empid, empname   FROM EmpCTEJanet   WHERE lvl = 2      以下为结果集:      empid    empname   ----------- -------------------------   11     David   12     Ron   13     Dan      该代码示例中比上一个代码示例增加的代码以粗体显示。递归成员中的筛选器 WHERE lvl < 2 被用作递归终止检查 — 当 lvl = 2 时,不会返回任何行,因而递归停止。外部查询中的筛选器 WHERE lvl = 2 用来移除上至级别 2 的所有级别。请注意,从逻辑上讲,外部查询中的筛选器 (lvl = 2) 本身就足以只返回所需的行。递归成员中的筛选器 (lvl < 2) 是出于性能原因而添加的 — 为了在返回 Janet 下的两个级别之后立即停止递归。      正如前面提到的那样,CTE 可以引用在同一批处理中定义的本地变量。例如,为了使查询更一般化,您可以使用变量而不是常量作为雇员 ID 和级别:      DECLARE @empid AS INT, @lvl AS INT   SET @empid = 3 -- Janet   SET @lvl  = 2 -- two levels   WITH EmpCTE(empid, empname, mgrid, lvl)   AS   (    SELECT empid, empname, mgrid, 0    FROM Employees    WHERE empid = @empid    UNION ALL       SELECT E.empid, E.empname, E.mgrid, M.lvl 1    FROM Employees as E   JOIN EmpCTE as M    ON E.mgrid = M.empid    WHERE lvl < @lvl   )   SELECT empid, empname   FROM EmpCTE   WHERE lvl = @lvl      您可以使用提示在已经调用一定数量的递归迭代之后强行终止查询。可以通过在外部查询的结尾添加 OPTION(MAXRECURSION value) 做到这一点,如以下示例所示:      WITH EmpCTE(empid, empname, mgrid, lvl)   AS   (    SELECT empid, empname, mgrid, 0    FROM Employees    WHERE empid = 1    UNION ALL       SELECT E.empid, E.empname, E.mgrid, M.lvl 1    FROM Employees as E   JOIN EmpCTE as M    ON E.mgrid = M.empid   )   SELECT * FROM EmpCTE   OPTION (MAXRECURSION 2)      以下为结果集:      empid    empname          mgrid    lvl   ----------- ------------------------- ----------- -----------   1      Nancy           NULL    0   2      Andrew          1      1   3      Janet           1      1   4      Margaret         1      1   10     Ina            4      2   7      Robert          3      2   8      Laura           3      2   9      Ann            3      2   .Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1   Statement terminated. Maximum recursion 2 has been exhausted before statement completion      可能返回(但是不能保证返回)迄今生成的结果,并且生成错误 530。您可能会想到使用 MAXRECURSION 选项实现以下请求:使用 MAXRECURSION 提示而不是递归成员中的筛选器返回 Janet 下两个级别的雇员:      WITH EmpCTE(empid, empname, mgrid, lvl)   AS   (    SELECT empid, empname, mgrid, 0    FROM Employees    WHERE empid = 3    UNION <
您需要登录后才可以回帖 登录 | 注册

本版积分规则

快速回复 返回顶部 返回列表