查询机构及其各子机构的工单处理情况

Question

表1：机构表(机构Id，机构名称，父级机构Id , 机构等级)
company_id , company_name , parent_company_id , company_level表2：工单表(工单id, 工单所属市级机构id,工单所属县级机构id,工单所属门店机构id,工单状态[已处理、未处理] )
order_id , company_id_1 , company_id_2 , company_id_3 , order_state
输入某个机构ID，统计出该机构及其下属机构的工单处理情况。(机构id，机构名称，总工单数量，未处理工单数量，已处理工单数量)：
company_id , company_name , sum_order , not_read_order , read_order
比如：输入洛阳市机构ID(7302),则统计出：
company_id , company_name , sum_order , not_read_order , read_order
7302 ,              洛阳市                       100                  50                       50
730201           洛阳市孟津县             40                  10                       30
730202           洛阳市汝阳县             30                  11                       19
... 
...
...

Answer 1

仅供参考create table company(company_id int, company_name varchar2(500) ,  parent_company_id int, company_level int);  insert into company
  select 1,'洛阳市',0,1 from dual
  union all
  select 2,'洛阳市孟津县',1,2 from dual
  union all
  select 3,'洛阳市汝阳县',1,2 from dual
  union all
  select 4,'洛阳市汝阳县小李门店',2,3 from dual;
create table orders(
order_id int , company_id_1 int, company_id_2 int, company_id_3 int, order_state int);insert into orders
select 1001,1,2,4,0 from dual
union all
select 1002,1,2,4,1 from dual
union all
select 1003,1,3,0,1 from dual
union all
select 1004,1,3,0,0 from dual;select 
        a.company_id,
        a.company_name,
        count(1) as 总工单数量,
        sum(case when   a.order_state=0 then 1 else 0 end) as 未处理工单数量,
        sum(case when   a.order_state=1 then 1 else 0 end) as 已处理工单数量
from    (  
            
          select   a.*,
                   --o.order_state,
                   --o1.order_state,
                   --o2.order_state,
                   nvl(o.order_state,nvl(o1.order_state,o2.order_state)) as order_state
          from     (  
                    select t.*,level
                    from company t
                    start with t.company_id=1
                    connect by  prior t.company_id=t.parent_company_id
                    ) a
          left join    orders  o
          on           a.company_id=o.company_id_1               left join    orders  o1
          on           a.company_id=o1.company_id_2            left join    orders  o2
          on           a.company_id=o2.company_id_3 
          
          ) a      
group by   a.company_id,
        a.company_name  
order by      a.company_id