SQL Server 2014에서 계층 적 데이터를 표시하는 방법

내 의견에서 나는 왜 테이블이 필요한지 물었다 @CompanyRelationShip. 이것은 단지 엄청난 복잡성과 오류 가능성을 추가하는 것입니다.

내 제안은 첫 번째 테이블에만 의존합니다. 계층 구조 아래 에 배치하기 위해 105 및 151의 상위 ID를 어떻게 변경했는지보십시오 . 원칙을 보여주기 위해 150 미만 의 두 번째 자녀를 추가했습니다 .

DECLARE @Company TABLE (
    CompanyId INT
    ,RootCompanyId INT
    ,CompanyName VARCHAR(100)
    );

INSERT INTO @Company
VALUES (2,2,'ROOT')
,(106,2,'ABC')
,(105,106,'CDF')
,(3,3,'ROOT2')
,(150,3,'YXZ')
,(151,150,'XZX')
,(152,150,'Second below 150');

-쿼리

WITH recCTE AS
(
    SELECT CompanyId AS [RootId],CompanyName AS [RootName],*,1 AS HierarchyLevel FROM @Company WHERE CompanyId=RootCompanyId
    UNION ALL
    SELECT rc.RootId,rc.RootName,c.*,rc.HierarchyLevel+1
    FROM @Company c
    INNER JOIN recCTE rc ON c.RootCompanyId=rc.CompanyId AND c.CompanyId<>rc.CompanyId
)
SELECT RootId
      ,RootName
      ,RootCompanyId AS [PrevId]
      ,CompanyId
      ,CompanyName
      ,HierarchyLevel 
FROM recCTE rc
ORDER BY RootId,HierarchyLevel;

결과

RootId  RootName    PrevId  CompanyId   CompanyName     HierarchyLevel
2       ROOT        2       2           ROOT                1
2       ROOT        2       106         ABC                 2
2       ROOT        106     105         CDF                 3
3       ROOT2       3       3           ROOT2               1
3       ROOT2       3       150         YXZ                 2
3       ROOT2       150     151         XZX                 3
3       ROOT2       150     152         Second below 150    3

간단히 말해서 아이디어 :

• 우리는 AA 사용 재귀 CTE (오히려입니다 반복 실제로 개념).
• 첫 번째 SELECT ( 앵커 )는 두 ID가 일치하는 회사로 시작합니다.
• UNION ALL 이후 두 번째 SELECT 는 중간 결과 줄 에 연결 하여 다음 수준을 선택합니다.
• 두 개의 열 RootId및 RootName단지를 통과하여 최종 세트에 표시합니다.

이는 HierarchyLevel라인 내의 위치이므로 ROOT 내에 105를 배치 하지만 106 아래에 배치 합니다.

도움이 되었기를 바랍니다...

주어진 구조에 대한 솔루션

말했듯이 주어진 구조는 최선의 선택이 아니므로 변경해야합니다. 그러나 이것을 고수해야한다면 다음과 같이 시도해 볼 수 있습니다.

WITH recCTE AS
(
    SELECT CompanyId AS [RootId],CompanyName AS [RootName],*,1 AS HierarchyLevel FROM @Company WHERE CompanyId=RootCompanyId
    UNION ALL
    SELECT rc.RootId,rc.RootName,c.*,rc.HierarchyLevel+1
    FROM @Company c
    INNER JOIN recCTE rc ON c.RootCompanyId=rc.CompanyId AND c.CompanyId<>rc.CompanyId
)
SELECT rc.CompanyId
      ,rc.CompanyName  
      ,COALESCE(crs.PrimaryCompanyId,rc.RootCompanyId) AS ComputedPrevId
      ,COALESCE(c1.CompanyName,rc.RootName) AS ComputedPrevName
      ,rc.RootId
      ,rc.RootName
FROM recCTE rc
LEFT JOIN @CompanyRelationShip  crs ON rc.CompanyId=crs.CompanyId AND rc.RootCompanyId<>crs.PrimaryCompanyId
LEFT JOIN @Company c1 ON crs.PrimaryCompanyId=c1.CompanyId
ORDER BY rc.RootId,rc.HierarchyLevel;

이것은 먼저 재귀 CTE를 사용하여 루트 회사 아래의 하위 항목을 찾고 관계 테이블에서 해당 줄을 찾으려고 시도합니다.

SELECT *열 목록 대신 사용 하면 전체 세트를 볼 수 있습니다. 사용 LEFT JOIN하면 클로스가 만나지 않을 NULL때 반환 됩니다 ON.

COALESCE NULL이 아닌 첫 번째 값을 반환하므로 원하는 값을 반환합니다.