oracle with 使用

oracle查询过程中，取查询结果中某些特别的数据，可以使用with语句，避免很多嵌套的复杂查询

下面是例子：

1.取结果中第一条数据、最后一条数据、非第一条最后一条数据中状态为‘Y’的数据

with tmp as
 (select rownum rn, t.*
    from (select ad.*
            from t_srv_addresscollection ad
           where ad.collection_time >= trunc(sysdate-1 )
             and ad.collection_time < trunc(sysdate)
             and ad.driver_code = '240209'
           order by ad.collection_time) t)
select *
  from tmp
 where (tmp.rn = 1)
    or (tmp.rn <> 1 and tmp.rn <> (select max(rn) from tmp) and
       tmp.is_stop = 'Y')
    or (tmp.rn = (select max(rn) from tmp))

2.with   insert 插入语句

/**
  统计快递员 所属 经营本部,事业部,大区,小区,营业部(或者 点部)
  */
  for c in (select v.empcode, v.deptcode from v_emp_courier v) loop
    insert into t_evs_courier_info
      with t as
       (select d.deptcode, d.deptname, d.deptlevel
          from t_org_department d
         where d.active = 'Y'
         start with d.deptcode = c.deptcode
        connect by nocycle prior d.parent_org_code = d.deptcode)
      select sys_guid(),
             sysdate,
             c.empcode,
             v_product_date,
             t3.deptcode,
             t3.deptname,
             t4.deptcode,
             t4.deptname,
             t5.deptcode,
             t5.deptname,
             t6.deptcode,
             t6.deptname,
             t7.deptcode,
             t7.deptname
        from (select t.deptcode, t.deptname from t where t.deptlevel = 3) t3,
             (select t.deptcode, t.deptname from t where t.deptlevel = 4) t4,
             (select t.deptcode, t.deptname from t where t.deptlevel = 5) t5,
             (select t.deptcode, t.deptname from t where t.deptlevel = 6) t6,
             (select t.deptcode, t.deptname from t where t.deptlevel = 7) t7;
  end loop;