oracle中的WITH关键字，相当于是试图-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_12247/article/details/82646029

本文介绍了一个复杂的SQL查询案例，该查询涉及多个表的连接、子查询及聚合函数的使用。通过对两个WITH子句的定义，实现了按月和按年的数据汇总，并通过连接操作将这些汇总数据组合在一起。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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