如何跟踪递归点在哪里正确导致sql？ [英] How to track recursive point where is exactly causing in sql ?

问题描述

我有数据库，其中员工经理关系正在某处发生，导致最大递归限制



例如A-> B-> c-> A（员工经理关系）现在我在这个数据库中有这么多公司和这么多员工..



基本上我正在为下面代码中的每个公司尝试

 声明 
  @compID   as  int = 45，
 < span class =code-sdkkeyword> @ roleName   as   varchar （ 50 ）= '  manager' 
 
 
 开始 
  WITH  CTE_Emp（Id，EmpName，ManagerID，[Level] ]，RoleName） A. S  
（
  SELECT  
 t1.Id，
 t1.FirstName + ' ' + t1.LastName '  EmpName'，
 t1.ManagerId，
  1 ，
（ SELECT  r1.Name  FROM  [角色]  as  r1  with （ nolock ） WHERE  r1.Id = t1.Role_Id）'  RoleName' 
  FROM 员工  t1  （ nolock ）
  WHERE  t1.Id  in （选择 e.Id 来自员工 as  e  with （ nolock ）） AND  t1.IsDeleted =  0   AND  t1.Status =  1   AND  t1.Company_Id =  @compID  
 
  UNION   ALL  
 
  SELECT  
 t1.Id，
 t1.FirstName + ' ' + t1.LastName '  EmpName'，
 t1.ManagerId，
（ t2。[等级] +  1 ），
（ SELECT  r1.Name  FROM  [角色]  as  r1  with （ nolock ） WHERE  r1.Id = t1.Role_Id ）'  RoleName' 
  FROM 员工 as  t1  with （ nolock ） 
  INNER   JOIN  CTE_Emp  as  t2  on  t1.ManagerId = t2.Id 
  WHERE  t1.IsDeleted =  0   AND  t1。[Status] =  1   AND  t1.Company_Id =  @compID  
） 
 
 选择 Id，EmpName，ManagerID，[Level]，RoleName 来自 CTE_Emp 其中 RoleName = ISNULL（ @ roleName ，RoleName）
 
 结束 
 
  GO  

是否有任何方式我可以通过所有commpany id并追踪哪个companyId导致问题...和然后我会检查特定的公司员工，在那里我可以用任何方式发送所有员工进行检查，并且能够追踪哪个员工正在考虑递归问题？

解决方案

这样的东西应该有效：

  WITH  cteData（Id，ManagerId，EmployeePath ，IsLoop）作为 
（
  SELECT  
 Id，
 ManagerId，
 '  / ' + CAST（Id  As   varchar （max））+ '  /'，
  CASE  
 < span class =code-keyword> WHEN  ManagerId = Id 那么 CAST（ 1  < span class =code-keyword>作为 位<​​/ span>）
  ELSE  CAST （ 0  作为 位<​​/ span>）
  END  
  FROM  
员工
 
  UNION   ALL  
 
  SELECT  
 D.Id，
 E.ManagerId，
 D.EmployeePath + CAST（E.Id 作为  varchar （max））+ '  /'，
  CASE  
  WHEN  D.EmployeePath 赞 ' ％' + CAST（E .Id 作为  varchar （max））+ ' ％' 那么 CAST（ 1  作为 位<​​/ span>）
  ELSE  CAST（ 0  作为 位<​​/ span> ）
  END  
  FROM  
 cteData  As  D 
  INNER   JOIN 员工 As  E 
  ON  E.Id = D.ManagerId 
  WHERE  
 D.IsLoop =  0  
）
  SELECT  
 Id，
 EmployeePath 
  FROM  
 cteData 
  WHERE  
 IsLoop =  1  
; 

给你一个循环的所有员工的列表，以及形成循环的员工ID列表。

---

编辑：

根据JörgenAndersson在评论中发布的建议，这应该是以更好的性能为您提供相同的结果：

  WITH  cteData（Id，ManagerId，EmployeePath，Distance） 作为 
（
  SELECT  
 Id，
 ManagerId ，
 CAST（Id  As   varchar （max）），
  0  作为距离
  FROM  
员工
 
  UNION  所有 
 
  SELECT  
 D.Id，
 E.ManagerId，
 D.EmployeePath + ' > ' + CAST（E.Id  As   varchar （max）），
 D.Distance +  1  
  FROM  
 cteData 作为 D 
  INNER   JOIN 员工 As  E 
  ON  E.Id = D.ManagerId 
  WHERE  
 D.ManagerId！= D.Id 
 和 
 E.ManagerId！= E.Id 
）
  SELECT  
 Id，
 EmployeePath，
距离
  FROM  
 cteData 
  WHERE  
 ManagerId = Id 
 和 
距离！=  0  
; 

Hi ,

I have database , in which employee manager relationship is happening somewhere which is causing max recurssion limit

for example A->B->c->A (Employee manager relationship) now i have so many companies and so many employees in that database ..

basically i am trying for each and everycompany from below code

Declare
 @compID as int=45,
 @roleName as varchar(50) = 'manager'


Begin
WITH CTE_Emp(Id, EmpName, ManagerID, [Level], RoleName) AS
   (
    SELECT
     t1.Id,
     t1.FirstName + ' ' + t1.LastName 'EmpName',
     t1.ManagerId,
     1,
     (SELECT r1.Name FROM [Role] as r1 with (nolock) WHERE r1.Id = t1.Role_Id) 'RoleName'
    FROM Employee as t1 with (nolock)
    WHERE t1.Id  in (select e.Id from Employee as e with (nolock)) AND t1.IsDeleted = 0 AND t1.Status = 1 AND t1.Company_Id = @compID

    UNION ALL

    SELECT
     t1.Id,
     t1.FirstName + ' ' + t1.LastName 'EmpName',
     t1.ManagerId,
     (t2.[Level] + 1),
     (SELECT r1.Name FROM [Role] as r1 with (nolock) WHERE r1.Id = t1.Role_Id) 'RoleName'
    FROM Employee as t1 with (nolock)
    INNER JOIN CTE_Emp as t2 on t1.ManagerId = t2.Id
    WHERE t1.IsDeleted = 0 AND t1.[Status] = 1 AND t1.Company_Id = @compID
   )

select Id, EmpName, ManagerID, [Level], RoleName from CTE_Emp where RoleName = ISNULL(@roleName, RoleName)

End

GO

is there is any way from which i can pass all commpany id and track down on which companyId it is causing an issue .. and then i will check for perticular company employee where again is there is any way where i can send all employee to check and can be able to track down on which employee is caussing recurssion problem ??

解决方案

Something like this should work:

WITH cteData (Id, ManagerId, EmployeePath, IsLoop) As
(
    SELECT
        Id,
        ManagerId,
        '/' + CAST(Id As varchar(max)) + '/',
        CASE
            WHEN ManagerId = Id THEN CAST(1 As bit)
            ELSE CAST(0 As bit)
        END
    FROM
        Employee

    UNION ALL

    SELECT
        D.Id,
        E.ManagerId,
        D.EmployeePath + CAST(E.Id As varchar(max)) + '/',
        CASE
            WHEN D.EmployeePath Like '%' + CAST(E.Id As varchar(max)) + '%' THEN CAST(1 As bit)
            ELSE CAST(0 As bit)
        END
    FROM
        cteData As D
        INNER JOIN Employee As E
        ON E.Id = D.ManagerId
    WHERE
        D.IsLoop = 0
)
SELECT
    Id,
    EmployeePath
FROM
    cteData
WHERE
    IsLoop = 1
;

That will give you the list of all employees which are part of a loop, with the list of employee IDs that form the loop.

---

EDIT:
Based on the suggestion posted by Jörgen Andersson in the comments, this should give you the same results with better performance:

WITH cteData (Id, ManagerId, EmployeePath, Distance) As
(
    SELECT
        Id,
        ManagerId,
        CAST(Id As varchar(max)),
        0 As Distance
    FROM
        Employee

    UNION ALL

    SELECT
        D.Id,
        E.ManagerId,
        D.EmployeePath + ' > ' + CAST(E.Id As varchar(max)),
        D.Distance + 1
    FROM
        cteData As D
        INNER JOIN Employee As E
        ON E.Id = D.ManagerId
    WHERE
        D.ManagerId != D.Id
    And
        E.ManagerId != E.Id
)
SELECT
    Id,
    EmployeePath,
    Distance
FROM
    cteData
WHERE
    ManagerId = Id
And
    Distance != 0
;

这篇关于如何跟踪递归点在哪里正确导致sql？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！