Рекурсия CTE для получения иерархии деревьев

Мне нужно получить упорядоченную иерархию дерева определенным образом. Соответствующая таблица выглядит примерно так (все поля ID являются уникальными идентификаторами, я упростил данные для примера):

EstimateItemID    EstimateID    ParentEstimateItemID     ItemType
--------------    ----------    --------------------     --------
       1              A                NULL              product
       2              A                  1               product
       3              A                  2               service
       4              A                NULL              product
       5              A                  4               product
       6              A                  5               service
       7              A                  1               service
       8              A                  4               product

Графический вид древовидной структуры (* обозначает "сервис" ):

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

Используя этот запрос, я могу получить иерархию (просто притворись, что "А" является уникальным идентификатором, я знаю, что это не в реальной жизни):

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

Это дает мне детей EstimateID 'A', но в том порядке, в котором он отображается в таблице. то есть:

EstimateItemID
--------------
      1
      2
      3
      4
      5
      6
      7
      8

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

1. each branch must be grouped
2. records with ItemType 'product' and parent are the top node 
3. records with ItemType 'product' and non-NULL parent grouped after top node 
4. records with ItemType 'service' are bottom node of a branch

Итак, порядок, в котором мне нужны результаты, в этом примере:

EstimateItemID
--------------
      1
      2
      3
      7
      4
      5
      8
      6

Что мне нужно добавить к моему запросу для выполнения этого?

Ответ 1

Попробуйте следующее:

;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 и SUBSTRING столбца Path....

Здесь SQLFiddle с образцом данных

Ответ 2

Это дополнение к превосходной идее Фабио сверху. Как я сказал в своем ответе на его оригинальный пост. Я повторно разместил его идею, используя более общие данные, имя таблицы и поля, чтобы облегчить другие действия.

Спасибо, Фабио! Великое имя, кстати.

Сначала некоторые данные для работы с:

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;

Ответ 3

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

BranchID = некоторый идентификатор, который однозначно идентифицирует ветвь. Простите меня за то, что вы не более конкретны, но я не уверен, что определяет отрасль для ваших нужд. В вашем примере показано двоичное дерево, в котором все ветки обращаются к корню.
ItemTypeID, где (например) 0 = Продукт и 1 = служба.
Parent = идентифицирует родителя.

Если они существуют на выходе, я думаю, вы должны использовать вывод из своего запроса как другой CTE или как предложение FROM в запросе. Порядок по BranchID, ItemTypeID, Parent.