with x1 as(

select

  case

    when time_segment in ('12-20h', '20-24h','24h以上') then '12h以上'

    else time_segment

  end time_segment

  , cons_num

  , org

  ,pre_org

from rc_arp_lv2_time_segment_cons_2

where 1=1 

and stat_time = (select max(stat_time) from rc_arp_lv2_time_segment_cons_2)

)

, x2 as(

select

 org

 ,pre_org

  , sum(cons_num) cons_num

,case when org = '福州' then 1 when org = '厦门' then 2 when org = '莆田' then 3

when org = '泉州' then 4 when org = '漳州' then 5 when org = '龙岩' then 6

when org = '三明' then 7 when org = '南平' then 8 when org = '宁德' then 9

end ord

from x1

where time_segment = '6-12h'

group by org, time_segment,pre_org

)

, x3 as(

select

 org

  ,pre_org

  , sum(cons_num) cons_num

from x1

where time_segment = '12h以上'

group by org, time_segment,pre_org

)

select 

regexp_replace(b.org,'供电所',null) as org

,a.cons_num  cons_num_6

,b.cons_num  cons_num_12

,a.cons_num+b.cons_num total_num

,a.ord

from x2 a

left join x3 b on a.org = b.org

order by a.ord,a.org

修改后：

    select  t2.org,t2.pre_org,sum(t2.cons_num) cons_num,t2.time_segment,t2.ord from (

     select t1.* from (

      select

case

when time_segment in ('12-20h', '20-24h','24h以上') then '12h以上'

else time_segment

end time_segment

, cons_num

, org

,pre_org

,case when org = '福州' then 1 when org = '厦门' then 2 when org = '莆田' then 3

              when org = '泉州' then 4 when org = '漳州' then 5 when org = '龙岩' then 6

              when org = '三明' then 7 when org = '南平' then 8 when org = '宁德' then 9

              end ord

from rc_arp_lv2_time_segment_cons_2

where 1=1 

and stat_time = (select max(stat_time) from rc_arp_lv2_time_segment_cons_2)) t1

  where time_segment in ('6-12h','12h以上')) t2 

group by t2.org,t2.pre_org,t2.time_segment,t2.ord

order by t2.ord,t2.org