Запрос дерева для определенного значения

Question

Я немного новичок в SQL, так что я определенно мог бы использовать некоторую помощь для уточнения общего дизайна конкретного запроса. Я приведу пример SQL того, что я пытаюсь сделать ниже. Он может содержать некоторые синтаксические ошибки, и я извиняюсь за это - я просто пытаюсь свалить дизайн, прежде чем приступить к работе и протестировать его!

Примечание: я имею 0 контроля над схемой проектирования, поэтому перепроектирование не вариант. В моих таблицах примеров может быть ошибка из-за недосмотра с моей стороны, но общая схема проектирования поиска снизу-вверх останется прежней. Я запрашиваю существующую базу данных, заполненную тоннами данных уже в ней.

Сценарий таков: есть дерево элементов. Каждый элемент имеет идентификатор и родительский идентификатор (схемы таблиц ниже). Родительский идентификатор - это рекурсивный внешний ключ для себя. Есть вторая таблица, которая содержит значения. Каждое значение имеет elementID, который является внешним ключом для таблицы элементов. Таким образом, чтобы получить значение определенной переменной для определенного элемента, вы должны объединить две таблицы.

Переменная иерархия идет снизу вверх в порядке наследования. Если у вас есть элемент и вы хотите получить значение его переменной, вы сначала посмотрите на этот элемент. Если у него нет значения, проверьте родительский элемент. Если это не проверяет родителя родителя - до самого верха. Каждая переменная гарантированно будет иметь значение к тому времени, когда вы достигнете вершины! (если я ищу переменную ID 21- я знаю, что 21 будет существовать. Если не снизу, то определенно сверху) Хотя самый нижний элемент дерева получает приоритет, если нижний элемент имеет значение для этой переменной, don не уходи дальше!

Таблицы будут выглядеть примерно так:

Element_Table
--------------
elementID (PK)
ParentID (FK to elementID)

Value_Table 
--------------
valueID (PK)
variableID
value (the value that we're looking for)
elementID (FK to Element_Table.elementID)

Итак, я собираюсь создать функцию, которая аккуратно (ключевое слово здесь. Красивый, чистый и эффективный код) выполняет поиск снизу вверх по дереву в поисках значения переменной. Как только я найду это - верни это значение и продолжай!

Вот пример того, что я думаю:

CREATE FUNCTION FindValueInTreeBottomUp
(@variableID int, @element varchar(50))
RETURNS varchar(50) 
AS
BEGIN
DECLARE @result varchar(50)
DECLARE @ID int
DECLARE @parentID int

SET @result = NULL, @ID = @element
WHILE (@result IS NULL)
    BEGIN
       SELECT @result = vals.value, @parentID = eles.ParentID
       FROM Value_Table vals 
       JOIN Element_Table eles 
       ON vals.elementID = eles.elementID
  WHERE eles.elementID = @ID AND vals.variableID = @variableID
  IF(@result IS NULL)
      @ID = @parentID
      CONTINUE
  ELSE
      BREAK
END

RETURN @result
END

Опять же, я прошу прощения, если есть какие-либо синтаксические ошибки. Все еще начинающий SQL и еще не запускал это! Я особенно новичок в функциях - я могу запросить весь день, но функции /sprocs все еще довольно новы для меня.

Итак, гуру SQL - можете ли вы придумать лучший способ сделать это? Дизайн столов не изменится; Я не имею никакого контроля над этим. Все, что я могу сделать, это создать запрос для проверки уже существующего дизайна.

Answer 1

Я думаю, что вы могли бы сделать что-то вроде этого (это не проверено, попробуйте в sql fiddle):

;with cte1 as (
    select e.elementID, e.parentID, v.value
    from Element_Table as e
        left outer join Value_Table as v on e.elementID = e.elementID and v.variableID = @variableID
), cte2 as (
    select v.value, v.parentID, 1 as aDepth
    from cte1 as v
    where v.elementID = @elementID
    union all
    select v.value, v.parentID, c.aDepth + 1
    from cte2 as c
    inner join cte1 as v on v.elementID = c.ParentID
    where c.value is null
)
select top 1 value
from cte2
where value is not null
order by aDepth

тестовая инфраструктура:

declare @Elements table (ElementID int, ParentID int)
declare @Values table (VariableID int, ElementID int, Value nvarchar(128))
declare @variableID int, @elementID int

select @variableID = 1, @elementID = 2

insert into @Elements
select 1, null union all
select 2, 1

insert into @Values
select 1, 1, 'test'

;with cte1 as (
    select e.elementID, e.parentID, v.value
    from @Elements as e
        left outer join @Values as v on e.elementID = e.elementID and v.variableID = @variableID
), cte2 as (
    select v.value, v.parentID, 1 as aDepth
    from cte1 as v
    where v.elementID = @elementID
    union all
    select v.value, v.parentID, c.aDepth + 1
    from cte2 as c
        inner join cte1 as v on v.elementID = c.ParentID
    where c.value is null
)
select top 1 value
from cte2
where value is not null
order by aDepth