postgre向上递归统计

--先找到有数据的记录

--将记录合并到树结构

--向上递归树结构 

--每个数据对应一个树节点记录，对树节点分组求和

--array [total] as leaf_path 使树节点可以获取到最远的后代节点的值

 with c as (
        select a.area_id, sum(a.total) total
        from forest_product_sale a,
             forest_product_info b
        where a.delete_flag != '1'
          and a.product_id = b.id
          and b.delete_flag != '1'
        group by a.area_id
      ),
           d as (
             select e.id, e.parent_id, e.name, c.total
             from forest_product_area e
                    left join c on e.id = c.area_id
           ),
           e as (
             with recursive g as (
               select id, parent_id, name, total, array [total] as leaf_path
               from d
               where id in (select area_id from c)
               union all
               select d.id, d.parent_id, d.name, d.total, g.leaf_path as leaf_path
               from d,
                    g
               where g.parent_id = d.id
             )
             select id, parent_id, name, sum(leaf_path[1]) as total
             from g
             group by id, parent_id, name
           )
select * from e