select job.unid,
job.biztype,
job.shpno,
job.shprname,
job.csgnname,
job.jobno,
jobother.ntfyname,
jobother.transhiptype,
Jobother.ServiceType,
Jobother.MCCShipment,
job.ishub,
jobother.bizscope,
jobother.frtterm,
job.bookingno,
job.shptype,
Sea.MoveType,
(case jobpoststatus.filestatus
when 'W' then
'1'
else
'0'
end) isAmend,
jobpoststatus.status status,
jobintfimp.rejectby rejectby
from job
left outer join jobother
on jobother.job_unid = job.unid
left outer join sea
on sea.job_unid = job.unid
inner join jobintfimp
on jobintfimp.job_unid = job.unid
left join (select distinct min(jps.job_export_unid) over(partition by jps.importunid) job_export_unid,
jps.status,
jps.importunid,
jps.filestatus
from jobpoststatus jps) jobpoststatus
on jobpoststatus.importunid = job.unid
where job.unid in (900032, 901424, 899081)
and ((jobpoststatus.job_export_unid is not null and jobpoststatus.job_export_unid =
(select min(jobpoststatus.job_export_unid)
from jobpoststatus
where jobpoststatus.importunid = job.unid))or jobpoststatus.job_export_unid is null)
select rownum,jobpoststatus.* from jobpoststatus
select distinct min(jobpoststatus.job_export_unid)over(partition by jobpoststatus.importunid) job_export_unid,status,importunid,filestatus from jobpoststatus
where jobpoststatus.importunid in (900032,901424,899081)
select * from jobpoststatus where importunid=900032
select * from jobpoststatus where importunid=899081
select jobpoststatus.importunid from jobpoststatus group by jobpoststatus.importunid having count(jobpoststatus.importunid)>1
select jobpoststatus.status,
jobpoststatus.importunid,
jobpoststatus.job_export_unid
from job
left join (select jobpoststatus.status,
jobpoststatus.importunid,
jobpoststatus.job_export_unid
from jobpoststatus
where jobpoststatus.job_export_unid in min(jobpoststatus.job_export_unid) over(partition by jobpoststatus.importunid)) jobpoststatus
on jobpoststatus.importunid = job.unid
where job.unid in (901424, 900032, 899081)
转载于:https://my.oschina.net/dingohaha60/blog/312734