TSQL CTE层次结构

我正试图在我的表上创建一个CTE，以引入员工的层次结构。

我有一个起点，那就是"主任"，我想找到每个向他们下面的每个人报告的人。

以下是我目前所拥有的：

;WITH EmpTable_CTE (FirstName, LastName, QID, Email) AS
    (
        SELECT      FirstName,
                    LastName,
                    QID,
                    Email
        FROM        EmployeeTable E
        WHERE       QID = '12345'
        UNION ALL
        SELECT      E.FirstName,
                    E.LastName,
                    E.QID,
                    E.Email
        FROM        EmployeeTable E
        INNER JOIN  EmpTable_CTE AS E2 ON E.MgrQID = E2.QID
    )
    SELECT * FROM EmpTable_CTE

这似乎为我提供了一份员工名单，但没有"层次结构"

如何使用FOR XML创建我要查找的层次结构？

<Director>Bob Smith</Director>
    <Direct>Jim Smith</Direct>
        <Direct>Employee 1</direct>
        <Direct>Employee 2</direct>
        <Direct>Employee 3</direct>
    <Direct>Bob Jones</Direct>
        <Direct>Employee 1</direct>
        <Direct>Employee 2</direct>
        <Direct>Employee 3</direct>
            <Direct>Employee A</direct>

我确信这只是把FOR XML线放在某个地方的问题，但我不能完全弄清楚。

更新：以下是示例数据的SQL Fiddle：

http://sqlfiddle.com/#！6/a48f6/1

这就是我所期望的来自小提琴的数据：

<Director>Jim Jones</Director>
    <Direct>Bob Jones</Direct>
        <Direct>Jake Jones</Direct>
        <Direct>Smith Jones</Direct>
            <Direct>Carl Jones</Direct>
                <Direct>Bobby Jones</Direct>
    <Direct>Danny Jones</Direct>
        <Direct>Billy Jones</Direct>

部分困难在于您所呈现的XML结构-如果您将其传递到解析器中，它将是平坦的，并且在不将名字和姓氏填充到属性中的情况下运行下面的过程的结果会使节点以混合内容出现（具有相同级别节点的文本）。

所以，我搜索了一下，在SE上找到了这个小宝石。为了适应你的需求，并从你的表中添加一些字段作为属性，我想出了这个：

CREATE FUNCTION dbo.EmpHierarchyNode(@QID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
  (SELECT QID AS "@ID", Email AS "@Email",
    FirstName + ' ' + LastName AS "@Name",
      CASE WHEN MgrQID = @QID
      THEN dbo.EmpHierarchyNode(QID)
      END
   FROM dbo.EmployeeTable
   WHERE MgrQID = @QID
   FOR XML PATH('Direct'), TYPE)
END
SELECT  QID AS "@ID", Email AS "@Email",
    FirstName + ' ' + LastName AS "@Name",
    dbo.EmpHierarchyNode(QID)
FROM dbo.EmployeeTable
WHERE MgrQID IS NULL
FOR XML PATH('Director'), TYPE

本质上，它在层次结构中向下遍历，递归地调用自己。如果您的输出是针对XML的，那么CTE是不够的。使用这个，以及我从你的样本数据中收集到的数据，我得到了这个结果：

<Director ID="1" Email="bsmith@someCompany.com" Name="Bob Smith">
  <Direct ID="2" Email="jsmith@someCompany.com" Name="Jim Smith">
    <Direct ID="4" Email="e1@someCompany.com" Name="Employee 1" />
    <Direct ID="5" Email="e2@someCompany.com" Name="Employee 2" />
    <Direct ID="7" Email="e4@someCompany.com" Name="Employee 4" />
  </Direct>
  <Direct ID="3" Email="bjones@someCompany.com" Name="Bob Jones">
    <Direct ID="6" Email="e3@someCompany.com" Name="Employee 3" />
    <Direct ID="8" Email="e5@someCompany.com" Name="Employee 5" />
    <Direct ID="9" Email="e6@someCompany.com" Name="Employee 6" />
  </Direct>
</Director>

希望这能有所帮助。

EDIT:最后一分钟SQLFiddle示例。

查看它是否满足您的要求：

;WITH EmpTable_CTE (FirstName, LastName, QID, Email) AS
    (
        SELECT      FirstName,
                    LastName,
                    QID,
                    Email
        FROM        EmployeeTable E
        WHERE       QID = 1
        UNION ALL
        SELECT      E.FirstName,
                    E.LastName,
                    E.QID,
                    E.Email
        FROM        EmployeeTable E
        INNER JOIN  EmpTable_CTE AS E2
        ON          E.MgrQID = E2.QID
    )
    SELECT LastName + ', ' + FirstName FROM EmpTable_CTE FOR XML PATH('Direct'), ROOT('Director'), TYPE

相关内容

最新更新

热门标签：