我写的SQL

        SELECT (CASE WHEN tabl.t1 is not null then  tabl.t1
        WHEN tabl.t2 is not null then  tabl.t2
        WHEN tabl.t3 is not null then  tabl.t3
        WHEN tabl.t4 is not null then  tabl.t4
        WHEN tabl.t5 is not null then  tabl.t5
        END) EC_TIME,
        ( CASE WHEN tabl.c1 is not null then tabl.c1
        WHEN tabl.c2 is not null then tabl.c2
        WHEN tabl.c3 is not null then tabl.c3
        WHEN tabl.c4 is not null then tabl.c4
        WHEN tabl.c5 is not null then tabl.c5
        END) EC_CITY_NAME ,

        tabl.mrtu CMR_TOTALCOMSUPTION,
        tabl.mrau CMR_AIRCONDITIONING,
        tabl.mrmu CMR_PRIMARYFACILITY,
        tabl.mrmus CMR_LIGHTINGANDOTHERS,
        tabl.stu OWNBASE_TOTALCOMSUPTION,
        tabl.sau OWNBASE_AIRCONDITIONING,
        tabl.smu OWNBASE_PRIMARYFACILITY ,
        tabl.sgu OWNBASE_LIGHTINGANDOTHERS,
        tabl.b1tu MAMAGEMENTHOUSE_TOTALUSAGE,
        tabl.b2gu MAMAGEMENTHOUSE_GENERATORUSAGE,
        tabl.b3tu OTHERS_TOTALUSAGE,
        tabl.b3gu OTHERS_GENERATORUSAGE

        FROM (
        SELECT m.cityname c1, m.EC_ITEM_TIME t1, s.cityname c2, s.EC_ITEM_TIME t2, b1.cityname c3, b1.EC_ITEM_TIME t3, b2.cityname
        c4, b2.EC_ITEM_TIME t4,b3.cityname c5, b3.EC_ITEM_TIME t5,
        NVL(m.machineroomtotalusage,0) mrtu, NVL(m.machineroomairconusage,0) mrau,NVL(m.machineroommainusage,0) mrmu,
        NVL(m.machineroomothersusage,0) mrmus,
        NVL(s.sitetotalusage,0) stu, NVL(s.siteairconusage,0) sau, NVL(s.sitemainusage,0) smu,
        NVL(s.sitegeneratorusage,0) sgu,
        NVL(b1.totalusage,0) b1tu, NVL(b1.generator_usage,0) b1gu, NVL(b2.totalusage,0) b2tu,
        NVL(b2.generator_usage,0) b2gu, NVL(b3.totalusage,0) b3tu,
        NVL(b3.generator_usage, 0) b3gu
        FROM(
        SELECT sr.NAME cityname, to_char( rcm.READTIME ,'yyyymm') as EC_ITEM_TIME, SUM(rcm.TOTAL_USAGE) machineroomtotalusage, SUM(rcm.AIRCON_USAGE)
        machineroomairconusage, SUM(rcm.MAIN_USAGE) machineroommainusage,
        SUM(rcm.GENERATOR_USAGE+rcm.OTHER_USAGE) machineroomothersusage

        FROM SYS_REGION sr
        full join RES_MACHROOM rm
        ON sr.CODE = rm.CITYID
        join RPT_CAP_MACHROOM rcm
        ON rcm.MACHROOMID = rm.ZGID
        GROUP BY sr.NAME, rcm.READTIME
        )m

        full join

        (
        SELECT sr.NAME cityname,to_char( rcs.READTIME ,'yyyymm') as EC_ITEM_TIME, SUM(rcs.TOTAL_USAGE) sitetotalusage,
        SUM(round(rcs.AIRCON_USAGE, 2)) as siteairconusage,
        SUM(rcs.MAIN_USAGE) sitemainusage, SUM(rcs.GENERATOR_USAGE) sitegeneratorusage
        FROM SYS_REGION sr
        full join RES_BTS rb
        ON sr.CODE = rb.CITYID
        join RPT_CAP_BTS rcs
        ON rcs.BTSID = rb.ZGID
        GROUP BY sr.NAME, rcs.READTIME
        )s

        on m.cityname = s.cityname AND m.EC_ITEM_TIME = s.EC_ITEM_TIME

        full join

        (
        SELECT sr.NAME cityname, to_char(rcb.READTIME ,'yyyymm') as EC_ITEM_TIME, SUM(rcb.TOTAL_USAGE) totalusage, SUM(rcb.GENERATOR_USAGE) generator_usage
        FROM SYS_REGION sr
        full join RES_BUILD rbb
        ON sr.CODE = rbb.CITYID
        join RPT_CAP_BUILD rcb
        ON rcb.BUILDID = rbb.ZGID AND rcb.BUILD_TYPE='1'
        GROUP BY sr.NAME, rcb.READTIME
        ) b1

        on m.cityname = b1.cityname AND m.EC_ITEM_TIME = b1.EC_ITEM_TIME

        full join

        (
        SELECT sr.NAME cityname,  to_char(rcb.READTIME ,'yyyymm') as EC_ITEM_TIME, SUM(rcb.TOTAL_USAGE) totalusage, SUM(rcb.GENERATOR_USAGE) generator_usage
        FROM SYS_REGION sr
        full join RES_BUILD rbb
        ON sr.CODE = rbb.CITYID
        join RPT_CAP_BUILD rcb
        ON rcb.BUILDID = rbb.ZGID AND rcb.BUILD_TYPE='3'
        GROUP BY sr.NAME, rcb.READTIME
        ) b3

        on m.cityname = b3.cityname AND m.EC_ITEM_TIME = b3.EC_ITEM_TIME

        full join
        (
        SELECT sr.NAME cityname, to_char(rcb.READTIME ,'yyyymm') as EC_ITEM_TIME , SUM(rcb.TOTAL_USAGE) totalusage, SUM(rcb.GENERATOR_USAGE) generator_usage
        FROM SYS_REGION sr
        full join RES_BUILD rbb
        ON sr.CODE = rbb.CITYID
        join RPT_CAP_BUILD rcb
        ON rcb.BUILDID = rbb.ZGID AND rcb.BUILD_TYPE='2'
        GROUP BY sr.NAME, rcb.READTIME
        ) b2

        on m.cityname = b2.cityname AND m.EC_ITEM_TIME = b2.EC_ITEM_TIME ) tabl