Oracle中with关键字的使用

 open p_cr1 for
      with sqla as
       (select d.*,
               (select c.STATICMONTH
                  from ly_zg_jzfbtstatic c
                 where c.ID = d.STATIC_ID) as STATICMONTH
          from tablename1 d
         where d.STATIC_ID in
               (select r.ID
                  from ly_zg_jzfbtstatic r
                 where to_char(r.STATICMONTH, 'yyyy') = varYear)
           and d.DISTNAME = '合计'),
      sqlb as
       (select d.*,
               (select c.STATICMONTH
                  from ly_zg_lzzfbtstatic c
                 where c.ID = d.STATIC_ID) as STATICMONTH
          from tablename2 d
         where d.STATIC_ID in
               (select r.ID
                  from ly_zg_lzzfbtstatic r
                 where to_char(r.STATICMONTH, 'yyyy') = varYear)
           and d.DISTNAME = '合计'),
      sqlc as
       (select d.*,
               (select c.STATICMONTH
                  from ly_zg_lzfbtstatic c
                 where c.ID = d.STATIC_ID) as STATICMONTH
          from tablename3 d
         where d.STATIC_ID in
               (select r.ID
                  from ly_zg_lzfbtstatic r
                 where to_char(r.STATICMONTH, 'yyyy') = varYear)
           and d.DISTNAME = '合计'),
      sqld as
       (select a.STATICMONTH,
               (a.newscount + b.newscount + c.newscount) newscount,
               (a.SYCOUNT + b.SYCOUNT + c.SYCOUNT) SYCOUNT,
               (a.NEWGCOUNT + b.NEWGCOUNT + c.NEWGCOUNT) NEWGCOUNT,
               (a.GYCOUNT + b.GYCOUNT + c.GYCOUNT) GYCOUNT,
               (a.MSCCOUNT + b.MSCCOUNT + c.MSCCOUNT) MSCCOUNT,
               (a.MGZFCOUNT + b.MGZFCOUNT + c.MGZFCOUNT) MGZFCOUNT,
               (c.MSCCOUNT + c.MGZFCOUNT) lzfMSCCOUNT,
               (b.MSCCOUNT + b.MGZFCOUNT) lzzfMSCCOUNT,
               (a.MSCCOUNT + a.MGZFCOUNT) jzfMSCCOUNT,
               (a.newscount + b.newscount + c.newscount + a.NEWGCOUNT +
               b.NEWGCOUNT + c.NEWGCOUNT) sum1,
               (a.MSCCOUNT + b.MSCCOUNT + c.MSCCOUNT + a.MGZFCOUNT +
               b.MGZFCOUNT + c.MGZFCOUNT) sum2
        
          from sqla a, sqlb b, sqlc c
         where a.STATICMONTH = b.STATICMONTH
           and c.STATICMONTH = a.STATICMONTH)
           --1
      select (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/01/01') as JAN,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/02/01') as FEB,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/03/01') as MAR,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/04/01') as APRIL,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/05/01') as MAY,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/06/01') as JUNE,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/07/01') as JULY,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/08/01') as AUJUST,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/09/01') as SEPT,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/10/01') as OCT,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/11/01') as NOV,
             (select newscount
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/12/01') as DECE
        from dual
      union all
      --2
      select null as JAN,
             null as FEB,
             null as MAR,
             null as APRIL,
             null as MAY,
             null as JUNE,
             null as JULY,
             null as AUGUST,
             null as SEPT,
             null as OCT,
             null as NOV,
             null as DECE
        from dual
      union all
      --3
      select (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/01/01') as JAN,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/02/01') as FEB,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/03/01') as MAR,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/04/01') as APRIL,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/05/01') as MAY,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/06/01') as JUNE,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/07/01') as JULY,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/08/01') as AUJUST,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/09/01') as SEPT,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/10/01') as OCT,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/11/01') as NOV,
             (select SYCOUNT
                from sqld
               where to_char(STATICMONTH, 'yyyy/mm/dd') = varYear || '/12/01') as DECE
        from dual；