对SQL Server中的特定行应用递归函数

Question

我想把这两个想法结合起来

1. 递归函数- http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

2. SQL为每一行调用存储过程而不使用游标

实际上我有两个单独工作

我可以让递归函数工作
我可以让一个存储过程在一行
上工作但是我很难弄清楚
如何让我的递归存储过程在选定的行

上工作

我也引用了这篇文章:
SQL递归函数-查找管理器

如果我有一个特定的行

id    mngr_id    person_id
--    -------    ---------
12       1777          923

我想计算有多少级别的CEO有一个mngr_id = null
(如上述例子之一)

问题的第二部分:
如果我想对随机数量的特定行执行此操作
这是有效的方法吗?

这段代码(根据上面的示例数据进行了调整)
从上面的例子1是优秀的，工作得很好

GO
WITH levels (mngr_id, person_id, id, Level)
AS
(
-- Anchor member definition
    SELECT e.mngr_id, e.person_id, e.id, 0 AS Level
    FROM MyEmployees AS e
    WHERE mngr_id IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.mngr_id, e.person_id, e.id, Level + 1
    FROM MyEmployees AS e
    INNER JOIN levels AS d
        ON e.mngr_id= d.person_id
)
-- Statement that executes the CTE
SELECT mngr_id, person_id, id, Level
FROM levels 
where id=@whatever_random_id_i_choose  -- <---- pseudocode
GO

注意:
我发帖的原因是我的伪代码不工作
当person_id不是唯一的

感谢大家的贡献

Answer 1

您最好使用递归公共表表达式。这样你也可以在视图中使用它。

WITH ORG_STRUCTURE
AS (
    SELECT 
         id
        ,mngr_id
        ,person_id
        ,0 AS c_level
    FROM occupancies
    WHERE person_id = @target_user_id

    UNION ALL
    SELECT
          id
        ,mngr_id
        ,person_id
        ,ORG.c_level  + 1 AS c_level
    FROM ORG_STRUCTURE ORG
        INNER JOIN occupancies MAN
            ON ORG.manager_id = MAN.person_id
    -- prevents recursion errors if you have an incomplete org structure
    WHERE c_level < 100
)
SELECT
     id
    ,mngr_id
    ,person_id
    ,MAX(c_level)
FROM ORG_STRUCTURE
WHERE mngr_id IS NULL
GROUP BY id
    ,mngr_id
    ,person_id

一个递归的CTX只能深入100层，所以如果你有一个不完整的组织结构，你需要在第二个查询中添加一个节流where子句(见注释)。

如果您有良好的组织结构，则可能不需要group by和MAX()。您可以将其放在一个存储过程@target_user_id中。如果您只想返回与CEO的距离，则可以将从CTE的选择限制为如下内容:

SELECT
    ,MAX(c_level)
FROM ORG_STRUCTURE
WHERE mngr_id IS NULL