从同一个表中选择父级和子级 [英] Select parent and child from the same table

问题描述

我有一个 emp 表，

I have a emp table,

    CREATE TABLE [dbo].[Emp](
    [EmpId] [int] NULL,
    [EmpName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ManagerId] [int] NULL
) ON [PRIMARY]

现在，将下面的值插入到表格中

Now, insert below values into the table

   Insert Into Emp Values(1,'A',0)
Insert Into Emp Values(2,'B',1)
Insert Into Emp Values(3,'C',2)
Insert Into Emp Values(4,'D',2)
Insert Into Emp Values(5,'E',4)
Insert Into Emp Values(6,'F',4)
Insert Into Emp Values(7,'G',4)
Insert Into Emp Values(8,'H',6)
Insert Into Emp Values(9,'I',5)
Insert Into Emp Values(10,'J',7)
Insert Into Emp Values(11,'K',4)

我想在 select 语句中列出员工姓名和他们的经理姓名.

I want to list employee name and their manager name in select statement.

我现在正在做的是创建一个临时表，其中包含所有经理姓名及其 ID.

What I am doing now is creating a temporary table which has all manager name and their Id.

然后根据 Id 从经理表中获取名称.

Then getting the name from the manager table based on Id.

但我知道这不是一个正确的方法，实际上它很复杂.

But I know this is not a correct way, in fact it is complex.

推荐答案

您应该为此使用递归 CTE(通用表表达式):

You should use a recursive CTE (Common Table Expression) for this:

-- define the recursive CTE and give it a name
;WITH Hierarchy AS
(
    -- "anchor" - top-level rows to select, here those with ManagerId = 0
    SELECT EmpId, EmpName, NULL AS 'MgrId', CAST(NULL AS NVARCHAR(50)) AS 'MgrName', 1 AS 'Level'
    FROM dbo.Emp
    WHERE ManagerId = 0

    UNION ALL

    -- recursive part - join an employee to its manager via ManagerId -> mgr.EmpId
    SELECT e.EmpId, e.EmpName, mgr.EmpId, mgr.EmpName, mgr.Level + 1 AS 'Level'
    FROM dbo.Emp e
    INNER JOIN Hierarchy mgr ON e.ManagerId = mgr.EmpId
)
SELECT * FROM Hierarchy

这篇关于从同一个表中选择父级和子级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！