递归CTE-查找经理以下的所有员工 [英] Recursive CTE-Find all Employees Below Manager

问题描述

我为此 SQLFIDDLE

CREATE TABLE [dbo].[Users](
    [userId] [int] ,
    [userName] [varchar](50) ,
    [managerId] [int] ,
   )

INSERT INTO dbo.Users
    ([userId], [userName], [managerId])
VALUES
(1,'Darry',NULL),
(2,'Cono',1),
(3,'Abros',2),
(4,'Natesh',1),
(5,'Ani',3),
(6,'Raju',5),
(7,'Pinky',5),
(8,'Miya',4)

我的要求就像显示所有员工等级在该特定经理下方

My requirement is like displaying all employees hierarchy below that particular manager

这是我尝试过的

WITH UserCTE AS (
  SELECT userId, userName, managerId, 0 AS EmpLevel
  FROM Users where managerId IS NULL

  UNION ALL

  SELECT usr.userId, usr.userName, usr.managerId, mgr.[EmpLevel]+1
  FROM Users AS usr
    INNER JOIN UserCTE AS mgr
      ON usr.managerId = mgr.userId where usr.managerId IS NOT NULL
)
SELECT * 
  FROM UserCTE AS u where u.ManagerId=3
  ORDER BY EmpLevel;

输出：

userName
--------
    Ani

我期望的输出是，如果我给出ManagerId 3，则应显示以下员工

The output that i am expecting is, if i give a ManagerId 3,then following employees should be shown

1.Abros
2.Ani
3.Raju
4.Pinky

任何人都可以帮忙

推荐答案

尝试一下。必须在CTE的锚点查询

Try this. Filter has to be applied in the Anchor query of CTE

WITH UserCTE
     AS (SELECT userId,
                userName,
                managerId,
                0 AS EmpLevel
         FROM   [Users]
         WHERE  managerId = 3
         UNION ALL
         SELECT usr.userId,
                usr.userName,
                usr.managerId,
                mgr.[EmpLevel] + 1
         FROM   [Users] AS usr
                INNER JOIN UserCTE AS mgr
                        ON usr.managerId = mgr.userId
         WHERE  usr.managerId IS NOT NULL)
SELECT *
FROM   UserCTE AS u
ORDER  BY EmpLevel; 

这篇关于递归CTE-查找经理以下的所有员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！