sql合并的问题？

将这两条语句合并成一条

第二条语句的?用到了第一条语句的查询结果

SQL code

select wa.file_digest,wa.shot_time,wa.shot_district,wa.program_name,wa.tml_id,ter.decardnumber
from watermark 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,watermark_file_status 
from file_info 
where file_digest = ? and file_acceptance_status= 2 and rownum <= 1

SQL code

select file_name,file_path,watermark_file_status 
from file_info 
where file_acceptance_status= 2 and rownum <= 1
and exists(select 1 from watermark 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)

自己顶一下先！

sql1中的字段保留

还得自己顶一下

这样子的写法,达不到要求嘛?

SQL code

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(watermark_file_status) from file_info where file_digest=wa.file_digest and file_acceptance_status= 2) as watermark_file_status
from watermark 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 '%%'

SQL code

select wa.file_digest,wa.shot_time,wa.shot_district,wa.program_name,wa.tml_id,ter.decardnumber
from watermark 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,watermark_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）

会的人有木有？

SQL code

那就不要用EXISTS要,用(+)

SQL code

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.watermark_file_status
from watermark wa,
     ixterminal ter,
     (select file_digest,file_name,file_path,watermark_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(+);