MSSQL递归层次查询 [英] MSSQL Recursive Hierarchial Query

问题描述

大家好，

我是MS SQL的新手，在创建一个层次结构查询时会遇到问题，该查询将显示一个雇员和离最高经理最近的经理.

例如，如果我有一个使用这种语法的表，

Hi Everyone,

I''m quiet new to MS SQL and I have problem in creating a hierarchial query that would display an employee and the nearest manager to the topmost manager.

For example, If i have a table with this syntax,

Create table Employee (EmpID int, Manager_ID int NULL, Title nvarchar(30));

具有以下值，

with the following values,

1 NULL CEO
2 1    VP
3 2    Senior Manager
4 3    Project Manager
5 4    Developer

对于ID为5的员工，我需要显示类似
的数据

For the Employee with ID 5, I need to display data like

5 4 Project Manager
5 3 Senior Manager
5 2 VP
5 1 CEO

以下查询将只给我一个层次结构，即； 5 4项目经理

The following query will give me only one level of hierarchy ie; 5 4 project manager

WITH ManagerHierarchy (EmpID, ManagerID, Title)
AS
(
-- Anchor member definition
    SELECT e.EmpID, e.ManagerID, e.Title 
    FROM dbo.Employee AS e
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.EmpID, e.ManagerID, e.Title
    FROM dbo.Employee AS e
    INNER JOIN ManagerHierarchy AS d
        ON e.ManagerID = d.EmpID
)
-- Statement that executes the CTE
SELECT EmpID, ManagerID, Title
FROM ManagerHierarchy

有什么方法可以重复此过程，以便使员工离我最近的经理到最顶层的经理?

任何输入将不胜感激. :)

在此先感谢
Ranganath P

Is there any way I can iterate this process so that I can get the Employee''s nearest Manager to the topmost Manager?

Any Inputs will be greatly appreciated. :)

Thanks in Advance
Ranganath P

推荐答案

一种实现方法可能是:

One way of doing this could be something like:

WITH ManagerHierarchy (EmpID, empid2, ManagerID, Title)
AS
(
-- Anchor member definition
    SELECT e.EmpID, e.EmpID, e.Manager_ID, e.Title 
    FROM dbo.Employee AS e
    WHERE not exists(select 1 from Employee e2 where e2.Manager_ID = e.EmpID) --Manager_ID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.EmpID, null,  e.Manager_ID, e.Title
    FROM dbo.Employee AS e
    INNER JOIN ManagerHierarchy AS d
        ON e.EmpID = d.ManagerID
)
-- Statement that executes the CTE
SELECT EmpID, ManagerID, max(empid2) over (partition by 1), Title
FROM ManagerHierarchy

我颠倒了树，以树中的最后一个元素为起点，然后递归向上.现在，如果empid2是链中的最后一个，则包含员工编号，否则为null.在调用部分中，我取了max(因为它的min或avg无关紧要，因为null不计算在内)，并使用max覆盖整个结果集，所以我用一个常数对其进行了分区.

抱歉，我的解释很糟糕(我现在在这里开始迟到了)，但希望代码更清晰:)

I reversed the tree to get the last element in the tree as a starting point and then recursively upwards. Now the empid2 contains the employee number if it''s the last in chain, otherwise null. And in the calling portion I took max (well it doesn''t matter if its min or avg since null''s don''t count) and to use max overt the whole result set i partitioned it with a constant.

I''m sorry the explanation is quite poor (it''s starting get late where I am) but hopefully the code is clearer :)

这篇关于MSSQL递归层次查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！