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

[look122333]

表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
...
...
...

[学海无涯-回头是岸]

两张表的关系是什么你得给出来吧

[qq_30534779]

仅供参考 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