Question

我要说的是:

CREATE TABLE Employees
(
EmployeeId int PRIMARY KEY NOT NULL,
ParentEmployeId int REFERENCES Employees(EmployeeId) NULL,
Name varChar(255)
)

所有记录都有一个主要识别资料,记录能够把另一个记录作为父母。 (My actual schema isn t about Workers, this is only an brief edition for example so if You ve got a better way of Hand original information it not germane to this dialogue.)

插入以下记录:

INSERT INTO Employees VALUES (1, NULL,  Company President 1 )
INSERT INTO Employees VALUES (2, NULL,  Company President 2 )

INSERT INTO Employees VALUES (3, 1,  Company President 1 - VP )
INSERT INTO Employees VALUES (4, 2,  Company President 2 - VP )

INSERT INTO Employees VALUES (5, 3,  Company President 1 - VP - Secretary )
INSERT INTO Employees VALUES (6, 4,  Company President 2 - VP - Secretary )

INSERT INTO Employees VALUES (7, 5,  Company President 1 - VP - Secretary - Sandwich Delivery )

这些词语代表:

Company President 1
    Company President 1 - VP
        Company President 1 - VP - Secretary
            Company President 1 - VP - Secretary - Sandwich Delivery
Company President 2
    Company President 2 - VP
        Company President 2 - VP - Secretary

我试图做的是,所有拥有全国人民力量联盟的员工。父母就业情况我想找到链中的最后一人,例如“。公司总裁 1 - VP - Secretary -WEPliver和“ Company President 2 - VP - Secretary

下面的CTE向我提供一切东西,包括nes,但我不敢肯定会从这里去。如果有可能,我想避免治疗。

而且,这是非常重要的,我在其他地方有逻辑,保证雇员只能有1个直属关系。因此,尽管该表在技术上允许,但<代码>Company President 1将永远不会有两种千兆字节。

WITH EmployeeRec(EmployeeId, ParentEmployeeId, Name, Level) AS
(
    SELECT
        EmployeeId,
        ParentEmployeId,
        Name,
        1 as [Level]
    FROM
        Employees
    WHERE
        ParentEmployeId IS NULL

    UNION ALL

    SELECT
        E.EmployeeId,
        E.ParentEmployeId,
        E.Name,
        R.[Level] + 1
    FROM
        Employees E
    INNER JOIN
        EmployeeRec R
    ON
        E.ParentEmployeId = R.EmployeeId
)
SELECT * FROM EmployeeRec

Answer 1

跟踪你的总经理,使你能够与最后一级一样,保留你所需要的记录。

WITH EmployeeRec(Master, EmployeeId, ParentEmployeeId, Name, Level) AS
(
    SELECT
        [Master] = EmployeeId,
        EmployeeId,
        ParentEmployeId,
        Name,
        1 as [Level]
    FROM
        Employees
    WHERE
        ParentEmployeId IS NULL

    UNION ALL

    SELECT
        R.Master,
        E.EmployeeId,
        E.ParentEmployeId,
        E.Name,
        R.[Level] + 1
    FROM
        Employees E
    INNER JOIN
        EmployeeRec R
    ON
        E.ParentEmployeId = R.EmployeeId
)
SELECT  *
FROM    EmployeeRec er
        INNER JOIN (
          SELECT  Master, Level = MAX(Level)
          FROM    EmployeeRec
          GROUP BY Master
        ) m ON m.Master = er.Master
               AND m.Level = er.Level

Answer 2

The key here is to keep Line of the high-level parent in the recursive CTE:

;WITH EmployeeRec(
   EmployeeId, ParentEmployeeId, UltimateGrandbossEmployeeId, Name, Level)
 AS
(
    SELECT
        EmployeeId,
        ParentEmployeeId,
        EmployeeId UltimateGrandbossEmployeeId,
        Name,
        1 as [Level]
    FROM
        Employees
    WHERE
        ParentEmployeeId IS NULL

    UNION ALL

    SELECT
        E.EmployeeId,
        E.ParentEmployeeId,
        R.UltimateGrandbossEmployeeId,
        E.Name,
        R.[Level] + 1
    FROM
        Employees E
    INNER JOIN
        EmployeeRec R
    ON
        E.ParentEmployeeId = R.EmployeeId
)

......组成一个中型企业,以掌握最底层的水平......

SELECT 
    UltimateGrandbossEmployeeId,
    Name,
    ROW_NUMBER() OVER (PARTITION BY UltimateGrandbossEmployeeId 
                       ORDER BY Level Desc ) BottomUp
 FROM EmployeeRec
)

......选择最深的孩子:

SELECT
    UltimateGrandbossEmployeeId, DeepestChildName
FROM
    Inter
WHERE
    BottomUp = 1

(将所有这些代码碎块合在一起,形成与两个电子计算器和一个<代码>SlectT的单一查询。)

成果:

1   Company President 1 - VP - Secretary - Sandwich Delivery
2   Company President 2 - VP - Secretary

您可在JOIN上查阅,以便按您的实际情况判断,获得大豆名称或履历。

友情链接