Как вычислить сумму значений в дереве с помощью SQL

Я сказал бы: создайте хранимую процедуру, вероятно, имеет лучшую производительность. Или если у Вас есть максимальное количество уровней, Вы могли бы создать подзапросы, но они будут иметь очень poort производительность.

(Или Вы могли получить SQL Server MS 2008 и получить новые функции иерархии... ;))

SQL в целом, как другие сказал, не обрабатывает хорошо такие отношения. Как правило, суррогатная таблица 'отношений' необходима (идентификатор, parent_id, уникальный ключ на (идентификатор, parent_id)), где:

• каждый раз Вы добавляете запись в 'таблице', Вас:

  INSERT INTO relations (id, parent_id) VALUES ([current_id], [current_id]);

  INSERT INTO relations (id, parent_id) VALUES ([current_id], [current_parent_id]);

  INSERT INTO relations (id, parent_id) SELECT [current_id], parent_id FROM relations WHERE id = [current_parent_id];

• имейте логику для предотвращения циклов

• удостоверьтесь, что обновления, удаления на 'отношениях' обрабатываются с хранимыми процедурами

Учитывая, что таблица, Вы хотите:

SELECT rel.parent_id, SUM(tbl.points)
FROM table tbl INNER JOIN relations rel ON tbl.id=rel.id
WHERE rel.parent_id <> 0
GROUP BY rel.parent_id;

Если бы Вы использовали Oracle DBMS, который был бы довольно прост, так как Oracle поддерживает древовидные запросы с ПОДКЛЮЧЕНИЕМ/НАЧИНАЕТ С синтаксисом. Для SQL Server я думаю, что Вы могли бы найти Общие Выражения Таблицы полезными

Деревья не работают хорошо с SQL. Если у Вас есть очень (очень очень) небольшое количество доступа для записи, Вы могли бы изменить древовидную реализацию для использования вложенных наборов, которые сделают этот запрос невероятно легким.

Пример (если я не ошибаюсь):

SELECT SUM(points) 
FROM users 
where left > x and right < y 

Однако любые изменения на дереве требуют касания значительной суммы строк. Вероятно, лучше просто сделать рекурсию в Вас клиент.

Хорошо, это дает Вам результаты, которые Вы ищете, но нет никаких гарантий, что я не пропустил что-то. Считайте это начальной точкой. Я использовал SQL 2005, чтобы сделать это, SQL, 2000 не поддерживает CTE's

WITH Parent (id, GrandParentId, parentId, Points, Level1Points, Level2Points)
AS
(
    -- Find root
    SELECT id,  
            0 AS GrandParentId,
            ParentId,
            Points,
            0 AS Level1Points,
            0 AS Level2Points
    FROM tblPoints ptr
    WHERE ptr.ParentId = 0

    UNION ALL (
    -- Level2 Points
    SELECT pa.GrandParentId AS Id,
            NULL AS GrandParentId,
            NULL AS ParentId,
            0 AS Points, 
            0 AS Level1Points,
            pa.Points  AS Level2Points
    FROM tblPoints pt
            JOIN Parent pa ON pa.GrandParentId = pt.Id 
    UNION  ALL
    -- Level1 Points
    SELECT pt.ParentId AS Id,
            NULL AS GrandParentId,
            NULL AS ParentId,
            0 AS Points, 
            pt.Points AS Level1Points,
            0 AS Level2Points
    FROM tblPoints pt
            JOIN Parent pa ON pa.Id = pt.ParentId AND pa.ParentId IS NOT NULL 
    UNION  ALL
    -- Points
    SELECT pt.id,
            pa.ParentId AS GrandParentId,
            pt.ParentId,
            pt.Points, 
            0 AS Level1Points,
            0 AS Level2Points
    FROM tblPoints pt
            JOIN Parent pa ON pa.Id = pt.ParentId AND pa.ParentId IS NOT NULL )
)
SELECT id, 
    SUM(Points) AS Points,  
    SUM(Level1Points) AS Level1Points,
    CASE WHEN SUM(Level2Points) > 0 THEN  SUM(Level1Points) + SUM(Level2Points) ELSE 0 END AS Level2Points
FROM Parent
GROUP BY id 
ORDER by id

У Вас есть несколько опций:

1. Используйте курсор и рекурсивный пользовательский вызов функции (это довольно медленно),
2. Составьте таблицу кэша, обновите ее на ВСТАВКЕ с помощью триггера (это - быстрое решение, но могло быть проблематично, если у Вас есть много обновлений основной таблицы),
3. Сделайте клиентское рекурсивное вычисление (предпочтительный, если у Вас нет слишком многих записей),

Если бы Вы работаете с деревьями, сохраненными в реляционной базе данных, я предложил бы смотреть "на вложенный набор" или "измененный обход дерева перед порядком". SQL будет так прост:

SELECT id, 
       SUM(value) AS value 
FROM table 
WHERE left>left\_value\_of\_your\_node 
  AND right<$right\_value\_of\_your\_node;

... и сделайте это для каждого узла, которым Вы интересуетесь.

Возможно, это поможет Вам: http://www.dbazine.com/oracle/or-articles/tropashko4 или использование Google.

Можно записать простую рекурсивную функцию, чтобы сделать задание. Мой MSSQL немного ржав, но он был бы похож на это:

CREATE FUNCTION CALC
(
@node integer,
)
returns 
(
@total integer
)
as
begin
    select @total = (select node_value from yourtable where node_id = @node);

    declare @children table (value integer);
    insert into @children   
    select calc(node_id) from yourtable where parent_id = @node;

    @current = @current + select sum(value) from @children;
    return
end