将这两条语句合并成一条第二条语句的?用到了第一条语句的查询结果
select wa.file_digest,wa.shot_time,wa.shot_district,wa.program_name,wa.tml_id,ter.decardnumber
from water wa,ixterminal ter where 1=1
and wa.tml_id = ter.tml_id
and wa.shot_district like '%%'
and wa.shot_time > ''
and wa.shot_time < ''
and wa.program_name like '%%'
and ter.decardnumber like '%%'select file_name,file_path,water_file_status
from file_info
where file_digest = ? and file_acceptance_status= 2 and rownum <= 1
select wa.file_digest,wa.shot_time,wa.shot_district,wa.program_name,wa.tml_id,ter.decardnumber
from water wa,ixterminal ter where 1=1
and wa.tml_id = ter.tml_id
and wa.shot_district like '%%'
and wa.shot_time > ''
and wa.shot_time < ''
and wa.program_name like '%%'
and ter.decardnumber like '%%'select file_name,file_path,water_file_status
from file_info
where file_digest = ? and file_acceptance_status= 2 and rownum <= 1
select file_name,file_path,water_file_status
from file_info
where file_acceptance_status= 2 and rownum <= 1
and exists(select 1 from water wa,ixterminal ter where 1=1
and wa.tml_id = ter.tml_id
and wa.shot_district like '%%'
and wa.shot_time > ''
and wa.shot_time < ''
and wa.program_name like '%%'
and ter.decardnumber like '%%' and wa.file_digest=file_info.file_digest)
select wa.file_digest,wa.shot_time,wa.shot_district,wa.program_name,wa.tml_id,ter.decardnumber,
(select max(file_name) from file_info where file_digest=wa.file_digest and file_acceptance_status= 2) as file_name,
(select max(file_path) from file_info where file_digest=wa.file_digest and file_acceptance_status= 2) as file_path,
(select max(water_file_status) from file_info where file_digest=wa.file_digest and file_acceptance_status= 2) as water_file_status
from water wa,ixterminal ter where 1=1
and wa.tml_id = ter.tml_id
and wa.shot_district like '%%'
and wa.shot_time > ''
and wa.shot_time < ''
and wa.program_name like '%%'
and ter.decardnumber like '%%'
select wa.file_digest,wa.shot_time,wa.shot_district,wa.program_name,wa.tml_id,ter.decardnumber
from water wa,ixterminal ter where 1=1
and wa.tml_id = ter.tml_id
and wa.shot_district like '%%'
and wa.shot_time > ''
and wa.shot_time < ''
and wa.program_name like '%%'
and ter.decardnumber like '%%'select file_name,file_path,water_file_status
from file_info
where file_digest = ? and file_acceptance_status= 2 and rownum <= 1
第二条语句的?用到了第一条语句的查询结果sql1中的file_digest,在sql2中都有多条记录1对多,但是只取file_acceptance_status= 2的那条(等于2的仅有一条),或者没有但是用空表示出来
例如
sql1中有100条不同的
file_digest,file_info
中也必须有100条
没有符合file_acceptance_status= 2的也显示出来(null)
select wa.file_digest,
wa.shot_time,
wa.shot_district,
wa.program_name,
wa.tml_id,
ter.decardnumber,
finfo.file_name,
finfo.file_path,
finfo.water_file_status
from water wa,
ixterminal ter,
(select file_digest,file_name,file_path,water_file_status
from file_info
where file_acceptance_status= 2 and rownum <= 1) finfo
where 1=1
and wa.tml_id = ter.tml_id
and wa.shot_district like '%%'
and wa.shot_time > ''
and wa.shot_time < ''
and wa.program_name like '%%'
and ter.decardnumber like '%%'
and wa.file_digest = finfo.file_digest(+);