select cod_mitem ,rot_ord ,plan_wkhr_m ,sum(plan_wkhr_no),
       case when status = '00' then plan_wkhr_no else 0 end  审核中,
       case when status = '01' then plan_wkhr_no else 0 end  通过审核,
       case when status = '02' then plan_wkhr_no else 0 end  退审
from
(
select cod_mitem,rot_ord,plan_wkhr_m,sum(plan_wkhr_n)+sum(plan_wkhr_o) as plan_wkhr_no,status 
from 
(
select c.cod_mitem,b.rot_ord,b.Plan_wkhr_m,a.plan_wkhr_n,a.plan_wkhr_o,a.start_date,a.status from web_wlms_o4sequence_user a 
       left join web_wlms_o3sequence b on a.rot_ord_id = b.id
       left join web_wlms_o2order c on b.num_ord_id = c.id
       where to_char(a.start_date,'yyyymmdd')>=(select to_char(sysdate,'yyyymmdd') from dual)
 )
 group by cod_mitem,rot_ord,plan_wkhr_m,status)
 group by cod_mitem,rot_ord,plan_wkhr_m

with web_wlms_o4sequence_user(cod_mitem, rot_ord, plan_wkhr_m, plan_wkhr_no, status)
as (
          select 'Z12009-M01', 15, '20.000', 3, '02' from dual
union all select 'Z12009-M01', 15, '20.000', 5, '00' from dual
union all select 'Z12009-M01', 15, '20.000', 2, '01' from dual)
select cod_mitem, rot_ord, plan_wkhr_m,
max(decode(status, '00', plan_wkhr_no)) status00,
max(decode(status, '01', plan_wkhr_no)) status01,
max(decode(status, '02', plan_wkhr_no)) status02
from web_wlms_o4sequence_user group by cod_mitem, rot_ord, plan_wkhr_m;

------解决方案--------------------

SQL code
with web_wlms_o4sequence_user(cod_mitem, rot_ord, plan_wkhr_m, plan_wkhr_no, status)
as (
          select 'Z12009-M01', 01, '10.000', 7, '01' from dual
union all select 'Z12009-M01', 14, '20.000', 13, '01' from dual
union all select 'Z12009-M01', 15, '20.000', 3, '02' from dual
union all select 'Z12009-M01', 15, '20.000', 2, '00' from dual
union all select 'Z12009-M01', 15, '20.000', 2, '01' from dual
union all select 'Z12009-M01', 15, '20.000', 6, '01' from dual
)
select cod_mitem 工程机号, rot_ord 工程令次, plan_wkhr_m 令次分配工时,
sum(plan_wkhr_no) 令次总派工时,
sum(decode(status, '00', plan_wkhr_no)) 审核中工时,
sum(decode(status, '01', plan_wkhr_no)) 审核通过,
sum(decode(status, '02', plan_wkhr_no)) 退审工时
from web_wlms_o4sequence_user group by cod_mitem, rot_ord, plan_wkhr_m
order by 1,2,3;

--执行结果
工程机号   工程令次         令次分配工时 令次总派工时           审核中工时             审核通过               退审工时
---------- ---------------- ------------ ---------------------- ---------------------- ---------------------- --------
Z12009-M01 1                      10.000 7                                             7                              
Z12009-M01 14                     20.000 13                                            13                             
Z12009-M01 15                     20.000 13                     2                      8                      3