Here is the table:

create table test_table ( id integer not null, name char(1) not null, parent_id integer, sum integer not null); INSERT INTO test_table VALUES(1, 'A', null, 300); INSERT INTO test_table VALUES(11, 'B', 1, 2340); INSERT INTO test_table VALUES(111, 'C', 11, 3200); INSERT INTO test_table VALUES(22, 'D', 1, 7540); INSERT INTO test_table VALUES(2, 'E', null, 6300); INSERT INTO test_table VALUES(3, 'F', 2, 8600); INSERT INTO test_table VALUES(101, 'G', 3, 3800); INSERT INTO test_table VALUES(102, 'H', 3, 9700);

Here is the code:

 SELECT distinct sum, name, CASE level WHEN 1 THEN (SELECT SUM(SUM) FROM test_table WHERE level > 1 START WITH parent_id is null CONNECT BY prior id = parent_id) WHEN 2 THEN (SELECT SUM(SUM) FROM test_table WHERE level > 2 START WITH parent_id is null CONNECT BY prior id = parent_id) WHEN 3 THEN null END AS SUM_SUM FROM test_table START WITH parent_id is null CONNECT BY prior id = parent_id order by name;

There is a specific question, there is a more general. Specific: how to calculate the salary of all subordinates, while the salary of the head is not taken into account. Common - how to separate the subordinates of one boss from another? Because in this query, an outpourse of all subordinates is issued, having a level greater than that of the superior.

Sample output:

 CREATE TABLE result_tbl ( id integer not null, name char(1) not null, parent_id integer, sum integer not null); INSERT INTO result_tbl VALUES(1, 'A', null, 12080); INSERT INTO result_tbl VALUES(11, 'B', 1, 3200); INSERT INTO result_tbl VALUES(111, 'C', 11, 0); INSERT INTO result_tbl VALUES(22, 'D', 1, 0); INSERT INTO result_tbl VALUES(2, 'E', null, 22100); INSERT INTO result_tbl VALUES(3, 'F', 2, 13500); INSERT INTO result_tbl VALUES(101, 'G', 3, 9700); INSERT INTO result_tbl VALUES(102, 'H', 3, 0);
  • And how does a boss record differ from a subordinate one? - gil9red
  • The id of the boss is the parent_id of the subordinate - daydark
  • Is the salary of subordinates the salary of one branch of recursion at the current level? You could, based on the above test data, write a table of what the result should be - Mike
  • Although I am starting to understand little by little ... I understand what the salary of subordinates in the hierarchy of a particular boss is. But what is "all subordinates", you need 2 digits - according to the hierarchy and the way you are doing it now - all higher level? And by the way, why the amount is not provided for level 3 - Mike
  • for level 3 the amount is not provided, because it has no subordinates. By the way, yes, if you solve a common task, and not this specific one, then level 3 may have a subordinate, then it turns out that CASE will not work? - daydark

1 answer 1

 select id, name, parent_id, ( select nvl(sum(sum),0) from test_table B start with B.parent_id=A.id -- Получать только подчиненных конкретной ветви connect by prior id=parent_id ) as sum_sum from test_table A

The second option:

 select id, name, sum(sum)-max(root_sum) from ( select connect_by_root(id) id, connect_by_root(name) name, connect_by_root(sum) root_sum, sum from test_table A connect by prior id=parent_id ) group by id, name

It does not have a start with indication, so connect by all entries in the table as root and for each entry creates all its subordinates at the output (many entries are duplicated). The connect_by_root function returns the specified field of the record with which the given output branch started. In this way, we can get the amounts for each individual root, but it will also include the sum of the root record itself, so at the final stage we will have to subtract it.