Запрос помощи относительно рекурсивного запроса или CTE

Question

Недавно я скачал AdventureWorks2012 для SQL Server Management Studio и нашел в Интернете несколько вопросов о базе данных. Я действительно застрял на одном и отчаянно нуждаюсь в некотором руководстве;

Показать иерархию управления от Рут Эллерброк до генерального директора Кена Санчеса

Каждый человек в организации имеет OrganizationalLevel в иерархии; с генеральным директором 0, Вице-президент 1, Главный инженер 2, Старший дизайнер инструментов 3и постепенно снижается. Самый низкий это 4.

Что я делаю: я соединяю два стола, [Person].[Person] А ТАКЖЕ [HumanResources].[Employee] вместе, чтобы получить FirstName, LastName,JobTitle, OrganizationalLevel

Select [Person].[Person].FirstName
     , [Person].[Person].LastName
     , [HumanResources].[Employee].OrganizationLevel 
from [HumanResources].[Employee] 
JOIN person.person ON ([HumanResources].[Employee].[BusinessEntityID]=[Person].[Person].[BusinessEntityID])

Насколько я понимаю, мне нужно использовать рекурсивный запрос или общее табличное выражение, но я действительно не знаю как.

Любая помощь будет очень признателен. И, пожалуйста, не стесняйтесь задавать любые вопросы для более подробной информации.

Спасибо.

Answer 1

Пример работы AdventureWorks заключается в использовании HierarchyId тип данных на Employee таблица - вам действительно не нужен рекурсивный CTE - подойдет обычный CTE.

Попробуйте что-то вроде этого:

-- define the CTE to get the "anchor" data - the row for Ruth Ellerbrook
;WITH Anchor AS 
(
    SELECT
        p.FirstName ,
        p.LastName ,
        e.OrganizationLevel,
        e.OrganizationNode
    FROM
        HumanResources.Employee e
    INNER JOIN 
        person.person p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE
        p.FirstName = 'Ruth'
        AND p.LastName = 'Ellerbrock'
)
SELECT 
    p.FirstName, 
    p.LastName,
    e.OrganizationLevel,
    CAST(e.OrganizationNode AS VARCHAR(20)) AS 'OrgNodeString'
FROM 
    HumanResources.Employee e
INNER JOIN 
    person.person p ON e.BusinessEntityID = p.BusinessEntityID
INNER JOIN
    Anchor a ON a.OrganizationNode.IsDescendantOf(e.OrganizationNode) = 1

Внешний SELECT присоединится к HumanResources.Employee а также Person.Person таблицы и получит все строки, где OrganizationNode столбец Рут Эллербрук является потомком другой строки - например, в нем будут перечислены все непосредственные начальники Рут Эллербрук, вплоть до генерального директора.