select * from (SELECT dp.pk_psndoc,dp.name,dp.begindate,DP.enddate,dpq.name as dpqname,dpq.stbegindate,dpq.stenddate
from (
--调配后记录
select rownum as dpnum,a.* from(
select hi_psnjob.PK_PSNDOC,ORG_DEPT.name,hi_psnjob.begindate,hi_psnjob.enddate from hi_psnjob
LEFT OUTER JOIN org_dept on hi_psnjob.pk_dept = org_dept.pk_dept
where hi_psnjob.trnsevent='3' ORDER BY pk_psndoc ,begindate) a
) dp
--调配前记录
LEFT OUTER JOIN (
select rownum as dpqnum, pk_psndoc,name,begindate as stbegindate,enddate as stenddate from (
--根据调配后部门主键找调配前记录
select rownum,b.*,org_dept.* from(
select hi_psnjob.* from hi_psnjob where pk_psnjob in(
select c.p from (
select pk_psnjob,lag(pk_psnjob,1,0) over (order by pk_psndoc ,begindate) p from hi_psnjob
) c where
c.pk_psnjob in (select pk_psnjob from hi_psnjob where hi_psnjob.trnsevent='3'))
ORDER BY pk_psndoc ,begindate
) b
LEFT OUTER JOIN
org_dept on b.pk_dept = org_dept.pk_dept
) )dpq on dp.dpnum =DPQ.dpqnum)
where name!=dpqname --是调配且找前后部门名称不一样来判断是调出还是调入
