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;