oracle中with的用法及用处

========
WITH
========
用于一个语句中某些中间结果放在临时表空间的SQL语句
如
WITH  channel_summary AS (
SELECT channels.channel_desc, SUM(amount_sold) AS channel_total
FROM sales, channels
WHERE sales.channel_id = channels.channel_id
GROUP BY channels.channel_desc
)
SELECT channel_desc, channel_total
FROM channel_summary
WHERE channel_total > (
SELECT SUM(channel_total) * 1/3
FROM channel_summary);

CHANNEL_DESC         CHANNEL_TOTAL
-------------------- -------------
Direct Sales             312829530

channel_summary表就是WITH生成的中间表

1： 这个临时表空间channel_summary  什么时候清除呢？是一个会话结束自动消失嘛？

     临时表在会话结束就自动被PGA清除
2：就这一功能来说，子查询就可以达到啊，为什么要用with呢？ 用with有什么好处，不会仅仅是 ...

     都能写，但执行计划不同的。当有多个相似子查询的时候，用with写公共部分，因为子查询结果在内存临时表中，执行效率当然就高啦。
     如果你后面需要不止一次地引用这个结果，那么WITH就会大大提高效率。
     代码的可读性好
 

WITH V_DOCID AS
 (
 SELECT b.doc_id id
    FROM tb_oa_doc_base b
    join tb_oa_doc_recv c
      on b.doc_id = c.doc_id
   where b.flow_status <> 0
     and ((c.sec_level = 4 and is_pub = 1) or
         b.doc_id in (select doc_id
                         from TB_OA_DOC_RECV_PER a
                        where 1=1
                          and a.is_readed = 1))
      or b.doc_id in (select w2.doc_id
                        from (select row_number() over(partition by w1.doc_id, w1.applay_id order by w1.doc_id, w1.applay_id) row_num,
                                     w1.doc_id,
                                     w1.applay_id
                                from (select t1.doc_id, t.applay_id
                                        from tb_wf_proc_log t
                                        join TB_OA_DOC_REF_PROC t1
                                          on t.processinst_id = t1.proinstid) w1) w2
                       where w2.row_num = 1 )
                       )
   select  *   from   V_DOCID      

 

 

-------------------------

2.两个with

--[9, 9, 133000, 133000]
with Month as
 (select max(ItemCode),
         max(ItemName),
         t.QUOTA_ID,
         max(CLEAR_DATE),
         nvl(decode(v.PAYTYPE_ID,
                    2,
                    decode(v.PAYKIND_ID, 0, sum(v.PAY_MONEY))),
             0) as zf_money,
         nvl(decode(v.PAYTYPE_ID,
                    2,
                    decode(v.PAYKIND_ID, 2, sum(v.PAY_MONEY))),
             0) as gz_money,
         nvl(decode(v.PAYTYPE_ID,
                    2,
                    decode(v.PAYKIND_ID, 1, sum(v.PAY_MONEY))),
             0) as qt_money,
         (select sum(a.plan_sum)
            from tb_plan_info a
           where a.QUOTA_ID = v.QUOTA_ID
             and a.is_cancel = 0
             and v.PAYTYPE_ID = 3
             and extract(month from ORDER_DATE) =9) as xd_money,
         nvl(decode(v.PAYTYPE_ID, 3, sum(v.PAY_MONEY)), 0) as zc_money
    from vw_tb_pay_info v, vw_tb_quota_info t
   where extract(month from to_date(CLEAR_DATE, 'yyyy-mm-dd')) = 9
     and v.QUOTA_ID = t.QUOTA_ID
     and v.PAYTYPE_ID != 5
     and v.AGENCY_CODE ='133000'
     and v.IS_CANCEL = 0
     and v.IS_CLEAR = 1
   group by t.QUOTA_ID, v.QUOTA_ID, v.PAYKIND_ID, v.PAYTYPE_ID),
YEAR AS
 (select max(ItemCode) ItemCode,
         max(ItemName) ItemName,
         t.QUOTA_ID QUOTA_ID1,
         max(CLEAR_DATE) CLEAR_DATE1,
         nvl(decode(v.PAYTYPE_ID,
                    2,
                    decode(v.PAYKIND_ID, 0, sum(v.PAY_MONEY))),
             0) as zf_money1,
         nvl(decode(v.PAYTYPE_ID,
                    2,
                    decode(v.PAYKIND_ID, 2, sum(v.PAY_MONEY))),
             0) as gz_money1,
         nvl(decode(v.PAYTYPE_ID,
                    2,
                    decode(v.PAYKIND_ID, 1, sum(v.PAY_MONEY))),
             0) as qt_money1,
         nvl(decode(v.PAYTYPE_ID, 3, max(t.COLLECT_SUM)), 0) as xd_money1,
         nvl(decode(v.PAYTYPE_ID, 3, sum(v.PAY_MONEY)), 0) as zc_money1,
         nvl(decode(v.PAYTYPE_ID, 3, max(t.AVAILMONEY)), 0) as AVAILMONEY1
    from vw_tb_pay_info v, vw_tb_quota_info t
   where v.QUOTA_ID = t.QUOTA_ID
     and v.PAYTYPE_ID != 5
     and v.AGENCY_CODE = '133000'
     and v.IS_CANCEL = 0
     and v.IS_CLEAR = 1
   group by t.QUOTA_ID, v.QUOTA_ID, v.PAYKIND_ID, v.PAYTYPE_ID)
SELECT rownum row_num, MONTH.*, YEAR.*
  FROM MONTH, YEAR
 where MONTH.quota_id = YEAR.quota_id1

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；
当现有的表不满足我们写如意sql，或者是根据现有的表写出如意sql太过复杂，所以Oracle为我们提出了with关键字，帮我们提供了很大的帮助

住，with后面的虚拟表必须用圆括号包起来，并且括号后面必须紧连接select关键字

并且用union all 连接的行中，每行中的列数据类型必须一致，注:null兼容任意数据类型