with t as(
select '1' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '2' wen_id,'a' lan_id, '01_1' zuo_id from dual
union all
select '3' wen_id,'b' lan_id, '01_1_2' zuo_id from dual
union all
select '4' wen_id,'c' lan_id, '01' zuo_id from dual
union all
select '5' wen_id,'a' lan_id, '09' zuo_id from dual
union all
select '6' wen_id,'a' lan_id, '09_1' zuo_id from dual
union all
select '7' wen_id,'b' lan_id, '010_1' zuo_id from dual
union all
select '8' wen_id,'c' lan_id, '010' zuo_id from dual
union all
select '9' wen_id,'c' lan_id, '09_1_1' zuo_id from dual
union all
select '10' wen_id,'a' lan_id, '010_1_1' zuo_id from dual
union all
select '11' wen_id,'a' lan_id, '010_1' zuo_id from dual
union all
select '12' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '13' wen_id,'b' lan_id, '01_2' zuo_id from dual

)
select tt.lan_id,
       MAX(nvl(decode(tt.org_id, '01', nm), 0)) 机构01,
       MAX(nvl(decode(tt.org_id, '09', nm), 0)) 机构09,
       MAX(nvl(decode(tt.org_id, '010', nm), 0)) 机构010
  from (
        
        select s.org_id, s.lan_id, count(s.wen_id) nm
          from (select t.wen_id,
                        t.lan_id,
                        substr(t.zuo_id,
                               1,
                               decode(instr(t.zuo_id, '_', 1),
                                      0,
                                      length(t.zuo_id) + 1,
                                      instr(t.zuo_id, '_') - 1)) org_id
                   from t) s
         group by s.lan_id, s.org_id
         order by s.org_id, s.lan_id) tt
 group by tt.lan_id
 order by tt.lan_id

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

这和上次那个差不多啊
SQL code

with t as(
select '1' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '2' wen_id,'a' lan_id, '01_1' zuo_id from dual
union all
select '3' wen_id,'b' lan_id, '01_1_2' zuo_id from dual
union all
select '4' wen_id,'c' lan_id, '01' zuo_id from dual
union all
select '5' wen_id,'a' lan_id, '09' zuo_id from dual
union all
select '6' wen_id,'a' lan_id, '09_1' zuo_id from dual
union all
select '7' wen_id,'b' lan_id, '010_1' zuo_id from dual
union all
select '8' wen_id,'c' lan_id, '010' zuo_id from dual
union all
select '9' wen_id,'c' lan_id, '09_1_1' zuo_id from dual
union all
select '10' wen_id,'a' lan_id, '010_1_1' zuo_id from dual
union all
select '11' wen_id,'a' lan_id, '010_1' zuo_id from dual
union all
select '12' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '13' wen_id,'b' lan_id, '01_2' zuo_id from dual

)
select tt.lan_id,
       MAX(nvl(decode(tt.org_id, '01', nm), 0)) 机构01,
       MAX(nvl(decode(tt.org_id, '09', nm), 0)) 机构09,
       MAX(nvl(decode(tt.org_id, '010', nm), 0)) 机构010
  from (
        
        select s.org_id, s.lan_id, count(s.wen_id) nm
          from (select t.wen_id,
                        t.lan_id,
                        substr(t.zuo_id,
                               1,
                               decode(instr(t.zuo_id, '_', 1),
                                      0,
                                      length(t.zuo_id) +