select substr(path, instr(path, '/', 1, 2) + 1, length(path)), sum(slaes) from (
select nvl(substr(sys_connect_by_path(user_name, '/'), 1, instr(sys_connect_by_path(user_name, '/'), '/', 1, 3) - 1), sys_connect_by_path(user_name, '/')) path, sales
from emp
where level > 1
start with user_no = 1
connect by pic_no = prior user_no
) group by path; |