SQL Server：CTE在where子句中回溯一级

Question

我希望对递归查询设置限制，但我不想失去最后一条我限制了递归的记录。

目前查询的样子是这样的：

WITH MyCTE (EmployeeID, FirstName, LastName, ManagerID, level, SPECIAL)
AS ( 
    SELECT a.EmployeeID, a.FirstName, a.LastName, a.ManagerID, 0 as Level
    FROM MyEmployees as a
    WHERE ManagerID IS NULL

    UNION ALL
    SELECT b.EmployeeID, b.FirstName, b.LastName, b.ManagerID, level + 1
    FROM MyEmployees as b
    INNER JOIN MyCTE ON b.ManagerID = MyCTE.EmployeeID
    WHERE b.ManagerID IS NOT NULL and b.LastName != 'Welcker' 
)
SELECT *
FROM MyCTE
OPTION (MAXRECURSION 25) 

经过经理Welcker限制，结果如下：

EmployeeID; FirstName; LastName; ManagerID; level
1           Ken        Sánchez   NULL       0

我希望把员工“Welcker”包含在内。问题是我只有需要列出的最后一个人的名字。命令结构下面有一些条目，但我不认识它们，也不想看到它们。

以下是我期望查询结果的示例。

EmployeeID FirstName LastName ManagerID level
1          Ken       Sánchez  NULL        0
273        Brian     Welcker  1           1

非常感谢您的帮助。

- forki

Answer 1

WITH MyCTE (EmployeeID, FirstName, LastName, ManagerID, level, NotMatched)
AS ( 
    SELECT a.EmployeeID, a.FirstName, a.LastName, a.ManagerID, 0 as Level, 0 AS NotMatched
    FROM MyEmployees as a
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT b.EmployeeID, b.FirstName, b.LastName, b.ManagerID, level + 1,
           CASE WHEN (MyCTE.LastName = 'Welcker' AND MyCTE.level = 1) THEN 1 ELSE MyCTE.NotMatched END
    FROM MyEmployees as b
    INNER JOIN MyCTE ON b.ManagerID = MyCTE.EmployeeID
)
SELECT *
FROM MyCTE
WHERE NotMatched != 1

- Aleksandr Fedorenko

---

我回到办公室后会尝试这个 :) 它看起来非常简单 :) - forki

如果我这样做，并使用低级别中的不同姓氏，我只会得到0级员工！我想要的是一个类似于Maxrecursion但有一个值的限制。就像选项（停在'rndLastname'处），然后CTE停止在这条路径上。例如，我有一个名为桑切斯的用户，他是业务的所有者和4个部门负责人。我想看到3个部门负责人及其员工，但不包括第4个经理的员工。但必须显示该经理！这个例子在这种情况下没有太多意义，但我认为我的意思很清楚。 - forki

我失去了'Welcker'级别以下的每个条目，即使他们没有'Welcker'作为经理。但我仍然需要这些。只有那些以'Welcker'为经理的人应该离开。但我们已经接近了 :) - forki

现在它使用了你之前的一个版本，非常感谢。我仍然不知道为什么它现在能工作。我使用了 WHERE MyCTE.LastName != 'Jiang' 而没有使用 -Level！我仍然不知道为什么它能工作，但它确实可以！！！ - forki

Answer 2

select distinct
   t.b_eid as empId,
   t.b_name as name, 
   case t.b_mid when 0 then null else t.b_mid end as managerId

   --convert(varchar,t.b_eid) + ', ' + 
   --t.b_name + ', ' + 
   --case t.b_mid when '0' then 'null' else convert(varchar,t.b_mid) end
      --as bvals

from(
   select
      coalesce(a.mid, 0) as a_mid,
      a.eid as a_eid,
      a.name as a_name,
      coalesce(b.mid, 0) as b_mid,
      b.eid as b_eid,
      b.name as b_name
   from 
      (values(null,1,'adam'),(null,2,'barb'),(201,3,'chris')) as a(mid,eid,name) cross join 
      (values(null,1,'adam'),(null,2,'barb'),(201,3,'chris')) as b(mid,eid,name)
) as t
where (t.a_mid = t.b_eid or t.a_mid = 0 or t.b_mid = 0) and t.a_name != 'chris'

- C H Vach

---

你忽略了原始查询的递归性质。我认为你的解决方案无法遍历完整个层次结构。 - user330315

是的，我不觉得我达到了标准。我喜欢这个问题。希望能看到更多答案。 - C H Vach

Answer 3

你能把那一行改成b.LastName = 'Welcker'吗？

- C H Vach

---

"OPTION (MAXRECURSION 25)" 会改变 "level + 1" 的行为吗？那太疯狂了！ - C H Vach

有其他10名员工，其中一些可能在今年更改。基本上，当Welcker出现时，它应该停止在结构中进一步深入，但仍然显示条目。不知道我是否表达清楚 :S - forki