트리 계층 구조를 가져오기 위한 CTE 재귀

트리 계층 구조를 가져오기 위한 CTE 재귀

저는 특정한 방법으로 나무의 순서 있는 계층 구조를 얻어야 합니다.문제의 표는 다음과 같습니다(모든 ID 필드는 고유 식별자이며, 예를 들어 데이터를 단순화했습니다).

추정항목 ID 추정치ID 상위 추정치ItemID 항목 유형--------------    ----------    --------------------     --------1 NULL 제품2 A 1 제품3 A 2 서비스4 A NULL 제품5 A 4 제품6 A 5 서비스7 A 1 서비스8 A 4 제품

트리 구조의 그래픽 보기(*는 '서비스'를 의미):

a___/ \___/         \1            4/ \          / \2   7*       5   8/            /3*           6*

이 쿼리를 사용하여 계층 구조를 가져올 수 있습니다('A'가 고유 식별자인 것처럼 가정하면 실제 ID가 아닙니다).

DECLARE @EstimateID uniqueidentifier
SELECT @EstimateID = 'A'

;WITH temp as(
    SELECT * FROM EstimateItem
    WHERE EstimateID = @EstimateID

    UNION ALL

    SELECT ei.* FROM EstimateItem ei
    INNER JOIN temp x ON ei.ParentEstimateItemID = x.EstimateItemID
)

SELECT * FROM temp

이것은 나에게 견적의 아이들을 줍니다.ID는 'A'이지만 표에 표시되는 순서대로 표시됩니다.i:

추정항목 ID--------------12345678

그러나 다음과 같은 제약 조건을 따르는 결과 집합을 가진 순서화된 계층 구조가 필요합니다.

각 분기는 그룹화되어야 합니다.ItemType 'product' 및 상위 노드가 있는 레코드가 상위 노드임상위 노드 뒤에 그룹화된 ItemType 'product' 및 NULL 상위가 아닌 레코드ItemType이 'service'인 레코드는 분기의 맨 아래 노드입니다.

이 예에서 결과가 필요한 순서는 다음과 같습니다.

추정항목 ID--------------12374586

이 작업을 수행하려면 쿼리에 무엇을 추가해야 합니까?

사용해 보십시오.

;WITH items AS (
    SELECT EstimateItemID, ItemType
    , 0 AS Level
    , CAST(EstimateItemID AS VARCHAR(255)) AS Path
    FROM EstimateItem 
    WHERE ParentEstimateItemID IS NULL AND EstimateID = @EstimateID

    UNION ALL

    SELECT i.EstimateItemID, i.ItemType
    , Level + 1
    , CAST(Path + '.' + CAST(i.EstimateItemID AS VARCHAR(255)) AS VARCHAR(255))
    FROM EstimateItem i
    INNER JOIN items itms ON itms.EstimateItemID = i.ParentEstimateItemID
)

SELECT * FROM items ORDER BY Path

와 함께Path행: 상위 노드별로 정렬

다음 기준으로 자식 노드를 정렬하려면ItemType각 레벨에 대해, 당신이 가지고 놀 수 있는 것보다.Level그리고.SUBSTRINGPath열...열...

여기에서 SQL 데이터 샘플과 함께 사용

이것은 위에서 본 Fabio의 훌륭한 아이디어에 추가된 것입니다.제가 그의 원래 게시물에 대한 답변에서 말한 것처럼.저는 다른 사람들이 쉽게 따라할 수 있도록 더 일반적인 데이터, 테이블 이름 및 필드를 사용하여 그의 아이디어를 다시 게시했습니다.

파비오 감사합니다!그나저나 좋은 이름입니다.

먼저 작업할 데이터:

CREATE TABLE tblLocations (ID INT IDENTITY(1,1), Code VARCHAR(1), ParentID INT, Name VARCHAR(20));

INSERT INTO tblLocations (Code, ParentID, Name) VALUES
('A', NULL, 'West'),
('A', 1, 'WA'),
('A', 2, 'Seattle'),
('A', NULL, 'East'),
('A', 4, 'NY'),
('A', 5, 'New York'),
('A', 1, 'NV'),
('A', 7, 'Las Vegas'),
('A', 2, 'Vancouver'),
('A', 4, 'FL'),
('A', 5, 'Buffalo'),
('A', 1, 'CA'),
('A', 10, 'Miami'),
('A', 12, 'Los Angeles'),
('A', 7, 'Reno'),
('A', 12, 'San Francisco'),
('A', 10, 'Orlando'),
('A', 12, 'Sacramento');

이제 재귀 쿼리:

-- Note: The 'Code' field isn't used, but you could add it to display more info.
;WITH MyCTE AS (
  SELECT ID, Name, 0 AS TreeLevel, CAST(ID AS VARCHAR(255)) AS TreePath
  FROM tblLocations T1
  WHERE ParentID IS NULL

  UNION ALL

  SELECT T2.ID, T2.Name, TreeLevel + 1, CAST(TreePath + '.' + CAST(T2.ID AS VARCHAR(255)) AS VARCHAR(255)) AS TreePath
  FROM tblLocations T2
  INNER JOIN MyCTE itms ON itms.ID = T2.ParentID
)
-- Note: The 'replicate' function is not needed. Added it to give a visual of the results.
SELECT ID, Replicate('.', TreeLevel * 4)+Name 'Name', TreeLevel, TreePath
FROM  MyCTE 
ORDER BY TreePath;

CTE 결과에 다음 사항을 추가해야 한다고 생각합니다.

1. BranchID = 분기를 고유하게 식별하는 일종의 식별자입니다.좀 더 구체적으로 말씀드리지 못해서 죄송합니다만, 무엇이 귀하의 필요에 맞는 지점을 식별하는지 잘 모르겠습니다.예제에서는 모든 분기가 루트로 다시 흐르는 이진 트리를 보여 줍니다.
2. 항목 유형ID(예: 0 = 제품 및 1 = 서비스).
3. 상위 =은(는) 상위를 식별합니다.

만약 그것들이 출력에 있다면, 나는 당신이 당신의 쿼리의 출력을 다른 CTE나 쿼리의 FROM 절로 사용할 수 있어야 한다고 생각합니다.지점 ID, 항목 유형별 주문신분증, 부모님.

언급URL : https://stackoverflow.com/questions/18106947/cte-recursion-to-get-tree-hierarchy