SQL-запрос: иерархическое объединение

У меня есть таблица, определяющая иерархию:

Create Table [example] (
    id          Integer   Not Null Primary Key,
    parentID    Integer       Null,
    largeData1  nVarChar(max) Null,
    largeData2  nVarChar(max) Null);
    -- largeData3...n also exist

Insert Into [example] (id, parentID, largeData1, largeData2)
Select 1, null, 'blah blah blah', null          Union
Select 2,    1, null,             null          Union
Select 3,    1, 'foo bar foobar', null          Union
Select 4,    3, null,             'lorem ipsum' Union
Select 5,    4, null,             null;

Диаграмма иерархии для этих данных:

Иерархическая диаграмма

Я хочу написать запрос, который будет возвращать одну строку для любого заданного значения [id]. Строка должна содержать информацию [id] и [parentID] этой строки. Он также должен содержать поля [largeData1...n]. Однако если поле largeData имеет значение null, оно должно перемещаться вверх по иерархии до тех пор, пока не встретится ненулевое значение для этого поля. Короче говоря, она должна работать так же, как и функция объединения, за исключением того, что она проходит через иерархию строк, а не набор столбцов.

Пример:

Где [id] = 1:

id:          1
parentID:    null
largeData1:  blah blah blah
largeData2:  null

Где [id] = 2

id:          1
parentID:    1
largeData1:  blah blah blah
largeData2:  null

Где [id] = 3

id:          3
parentID:    1
largeData1:  foo bar foobar
largeData2:  null

Где [id] = 4

id:          4
parentID:    3
largeData1:  foo bar foobar
largeData2:  lorem ipsum

Где [id] = 5

id:          5
parentID:    4
largeData1:  foo bar foobar
largeData2:  lorem ipsum

Пока что у меня это:

Declare @id Integer; Set @id = 5;

With heirarchy
    (id, parentID, largeData1, largeData2, [level])
As (
    Select id, parentID, largeData1,
           largeData2, 1 As [level]
    From example
    Where id = @id

    Union All

    Select parent.id, parent.parentID,
           parent.largeData1,
           parent.largeData2,
           child.[level] + 1 As [level]
    From example As parent
    Inner Join heirarchy As child
        On parent.id = child.parentID)

Select id, parentID,
   (Select top 1 largeData1
    From heirarchy
    Where largeData1 Is Not Null
    Order By [level] Asc) As largeData1,

   (Select top 1 largeData2
    From heirarchy
    Where largeData2 Is Not Null
    Order By [level] Asc) As largeData2

From example
Where [id] = @id;

Это возвращает результаты, которые я ищу. Однако, согласно плану запроса, он делает отдельный проход по иерархии для каждого поля largeData, которое я возвращаю.

Как сделать это более эффективным?

Очевидно, это упрощенная версия более сложной задачи. Окончательный запрос вернет данные в формате XML, поэтому любые решения, включающие предложение FOR XML, прекрасно подходят.

Я могу создать для этого агрегатную функцию CLR, если это поможет. Я еще не исследовал этот маршрут.


hierarchy coalesce sql-server-2005
person Chris Nielsen    schedule 21.08.2010    source источник

Ответы (1)


arrow_upward
6
arrow_downward

Я придумал это:

DECLARE @Id  int

SET @Id = 5


;WITH cte (Id, ParentId, SaveParentId, LargeData1, LargeData2)
 as (--  The "anchor", your target Id
     select
        ex.Id
       ,ex.ParentId
       ,ex.ParentId  SaveParentId  --  Not changed throughout the CTE
       ,ex.LargeData1
       ,ex.LargeData2
      from Example ex
      where ex.Id = @Id
     union all select
                 cte.Id
                ,ex.ParentId
                ,cte.SaveParentId  --  Not changed throughout the CTE
                 --  These next are only "reset" if they are null and a not-null
                 --  value was found at this level 
                ,isnull(ex.LargeData1, cte.LargeData2)  
                ,isnull(ex.LargeData2, cte.LargeData2)
      from Example ex
       inner join cte
        on cte.ParentId = ex.Id)
 select
   Id
  ,SaveParentId     ParentId
  ,max(LargeData1)  LargeData1
  ,max(LargeData2)  LargeData2
 from cte
 group by Id, SaveParentId

По сути, начните с целевого узла и пройдите вверх по дереву, заменив нулевые столбцы ненулевыми значениями, если и когда они будут найдены.

(Извините, но я не работаю с XML по выходным.)

person Philip Kelley    schedule 21.08.2010
comment
+1 за подталкивание ненулевых значений. Но использование MAX может быть проблематичным. Если в строке 3 выборочных данных указано afoo bar bar вместо foo bar bar, запрос для @id=5 вернет бла-бла-бла для largeData1. - person 8kb; 23.08.2010
comment
При переходе вверх по CTE, если на заданном уровне значение столбца равно null, оно заменяется значением на этом уровне, в противном случае оно остается неизменным. На каждом уровне создается одна строка. Таким образом, когда cte будет выполнено, значение для столбца во всех строках будет либо нулевым, либо первым встреченным значением. Агрегации игнорируют пустые значения, оставляя для выбора только одно значение max (или min). - person Philip Kelley; 23.08.2010