Hello. As in a recursive query using CTE, preserve the hierarchical structure and at the same time calculate the total salary not only at the top of the hierarchy, but also of all nodes, excluding the lowest ones that have no children. The source table looks like this.

enter image description here

It turned out to achieve the desired result using 2 CTE tables.

enter image description here

With the help of such a request

with C(id_working,salary,RootID,RootID_B) as ( select --0 as lvl, company_2.id_working, company_2.salary, company_2.id_working as RootID, company_2.id_boss as RootID_B from company_2 union all select --lvl+1, company_2.id_working, company_2.salary, C.RootID, C.RootID_B from company_2 inner join C on company_2.id_boss = C.id_working) , t1(lvl,bname, id_boss,id_working,path) as (select 1 AS lvl, t.bname, t.id_boss, t.id_working, '' AS path from company_2 t where t.id_boss is null union all select lvl+1, t2.bname, t2.id_boss,t2.id_working, ltrim((t1.path||','||t1.bname),',') AS path from company_2 t2 join t1 on t2.id_boss=t1.id_working ) SEARCH DEPTH FIRST BY path SET orderval select S_2.tree, S.sumsal, S_2.path --orderval --S_2.bname --RPAD('.', (lvl-1)*2, '.') || id AS tree, --C.lvl from company_2 full join ( select RootID,RootID_B, (sum(salary)-min(salary)) as sumsal, count(salary) as count_sal from C group by RootID, RootID_B having count(salary)>1 ) S on company_2.id_working = S.RootID full join (select orderval, id_working,RPAD(' ', (lvl-1)*2, ' ') || bname AS tree, path from t1) S_2 on company_2.id_working=S_2.id_working order by S_2.orderval [1]: https://i.stack.imgur.com/OiMVM.jpg [2]: https://i.stack.imgur.com/utIKf.jpg

But how to achieve the same results with 1 CTE?

  • one
    And how to do something if you need to spend an hour on something that would kill your data from the image in a text view? Give the data as text (ideally as insert to create a table) and a little explanation of what you need to get - Mike
  • and something tells me that really after the first CTE there is already enough data to group and display them. just need to think a bit about the formation of the desired sorting order. I think getting the path in the first part of the query could have helped - Mike
  • By the way, a very similar task (if not exactly such) ru.stackoverflow.com/a/585879/194569 - Mike
  • The task is really the same, thank you, but it is not difficult to implement such a thing conect by. And here by means of cte tables I somehow am stupid. In any case, thanks, I will understand :) - Maki Big
  • With CTE, the same thing, I would suggest forming a path from id so that there are no intersections. Your first CTE actually prepares the source data exactly the same as connect by. Your task is to group them correctly, apparently by root, and sort them (possibly by path or path from id) - Mike

2 answers 2

 with RCTE(root_id,name,root_sum,sum,id,path,lvl) as( select id, name, sum, sum, id, lpad(id,10,'0'), 0 from test_table union all select R.root_id, R.name, R.root_sum, T.sum, T.id, R.path||'/'||lpad(T.id,10,'0'), lvl+1 from RCTE R, test_table T where T.parent_id=R.id ) select root_id, RPAD(' ', (min(max_lvl))*2, '-') ||name, sum(sum)-max(root_sum), min(full_path) from ( select R.*, first_value(path) over(partition by id order by lvl desc) full_path, max(lvl) over(partition by id) max_lvl from RCTE R ) X group by root_id, name order by min(full_path)

For the correct output in the form of a tree, we get a path for each id, where the given id has the maximum nesting level (that is, the longest path in which it participates) and sorted by this path. for the correct indents, we first take the maximum level for each id. And when grouping by root, since we have all the records from this level and below, we take the minimum level of them (this is the root level)

  • I try to apply this code to my tables, I get the error "The result of string concatenation is too large", although making the code from your code is not from the ID, and there is no such error from the names. :( Can you have any idea what this is connected with? - Maki Big
  • @ MakiBig make a lpad for less, your id is now 4 characters, I think 6 should be enough for a long time. besides a separator / there for human readability, the sorting will work fine and if id is close to each other - Mike
  • Thanks a lot for your help. I redid the original table and apparently there was an error in the table itself. Your examples and explanations helped a lot. :) Thank! - Maki the Big
  • But I have a question for you again, forgive me for being so annoying. I want to make sure that their paths (that is, they themselves) are not indicated in front of the root records. I replace lpad (id, 10, '0') with '' (empty quotes) and get the same error associated with concatenation. - Maki the Big
  • @ MakiBolshoy And if something like cast('' as varchar2(2000)) - Mike
 with RCTE(root_id,name,root_sum,sum,id,path,lvl) as( select id, name, sum, sum, id, name, 0 from company_4 union all select R.root_id, R.name, R.root_sum, T.sum, T.id, R.path||','||T.name, lvl+1 from RCTE R join company_4 T on T.parent_id=R.id ) SEARCH DEPTH FIRST BY id SET orderval select RPAD(' ', (min(max_lvl))*2) ||name as tree, /*min(full_path) as bosses,name,*/ SUBSTR(min(full_path), 1, INSTR(min(full_path), ',', -1) -1), nullif(sum(sum)-max(root_sum),0) as sumsal from ( select R.*, first_value(path) IGNORE NULLS over(partition by id order by lvl desc) full_path, max(lvl) over(partition by id) max_lvl from RCTE R ) X group by root_id,name order by min(full_path);

my code version