select aa.FILE_ID,
to_char(create_date, 'yyyy-mm-dd') as create_date,
aa.archive_id,
aa.oper_id,
to_char(aa.oepr_date, 'yyyy-mm-dd hh24:mi:ss') as oepr_date,
aa.user_id,
aa.file_name,
aa.oper_sql,
aa.oper_status,
aa.status,
aa.file_format_id,
bb.file_oper_desc,
cc.user_true_name
from (select j.FILE_ID,
j.archive_id,
j.oper_id,
j.oepr_date as oepr_date,
j.user_id,
j.oper_sql,
j.oper_status,
k.FILE_NAME,
'审核' as status,
k.file_format_id
from file_his_operate j, file_using k
where j.file_id = k.file_id
and j.archive_id = k.archive_id
and k.status = 'Y'
union
select a.FILE_ID,
a.archive_id,
a.oper_id,
a.oepr_date as oepr_date,
a.user_id,
a.oper_sql,
a.oper_status,
b.FILE_NAME,
'未审' as status,
b.file_format_id
from file_his_operate a, file_using b
where a.file_id = b.file_id
and a.archive_id = b.archive_id
and b.status = 'N'
union
select c.FILE_ID,
c.archive_id,
c.oper_id,
c.oepr_date as oepr_date,
c.user_id,
c.oper_sql,
c.oper_status,
d.FILE_NAME,
'退回' as status,
d.file_format_id
from file_his_operate c, file_returned d
where c.file_id = d.file_id
and c.archive_id = d.archive_id
union
select e.FILE_ID,
e.archive_id,
e.oper_id,
e.oepr_date as oepr_date,
e.user_id,
e.oper_sql,
e.oper_status,
f.FILE_NAME,
'废弃箱' as status,
f.file_format_id
from file_his_operate e, file_deled f
where e.file_id = f.file_id
and e.archive_id = f.archive_id
and f.status = 'Y'
union
select g.FILE_ID,
g.archive_id,
g.oper_id,
g.oepr_date as oepr_date,
g.user_id,
g.oper_sql,
g.oper_status,
h.FILE_NAME,
'保留箱' as status,
h.file_format_id
from file_his_operate g, file_deled h
where g.file_id = h.file_id
and g.archive_id = h.archive_id
and h.status = 'N') aa
left join file_oper bb on aa.oper_id = bb.file_oper_id
left join user_sys cc on aa.user_id = cc.user_id
where 1 = 1
order by aa.oepr_date desc
to_char(create_date, 'yyyy-mm-dd') as create_date,
aa.archive_id,
aa.oper_id,
to_char(aa.oepr_date, 'yyyy-mm-dd hh24:mi:ss') as oepr_date,
aa.user_id,
aa.file_name,
aa.oper_sql,
aa.oper_status,
aa.status,
aa.file_format_id,
bb.file_oper_desc,
cc.user_true_name
from (select j.FILE_ID,
j.archive_id,
j.oper_id,
j.oepr_date as oepr_date,
j.user_id,
j.oper_sql,
j.oper_status,
k.FILE_NAME,
'审核' as status,
k.file_format_id
from file_his_operate j, file_using k
where j.file_id = k.file_id
and j.archive_id = k.archive_id
and k.status = 'Y'
union
select a.FILE_ID,
a.archive_id,
a.oper_id,
a.oepr_date as oepr_date,
a.user_id,
a.oper_sql,
a.oper_status,
b.FILE_NAME,
'未审' as status,
b.file_format_id
from file_his_operate a, file_using b
where a.file_id = b.file_id
and a.archive_id = b.archive_id
and b.status = 'N'
union
select c.FILE_ID,
c.archive_id,
c.oper_id,
c.oepr_date as oepr_date,
c.user_id,
c.oper_sql,
c.oper_status,
d.FILE_NAME,
'退回' as status,
d.file_format_id
from file_his_operate c, file_returned d
where c.file_id = d.file_id
and c.archive_id = d.archive_id
union
select e.FILE_ID,
e.archive_id,
e.oper_id,
e.oepr_date as oepr_date,
e.user_id,
e.oper_sql,
e.oper_status,
f.FILE_NAME,
'废弃箱' as status,
f.file_format_id
from file_his_operate e, file_deled f
where e.file_id = f.file_id
and e.archive_id = f.archive_id
and f.status = 'Y'
union
select g.FILE_ID,
g.archive_id,
g.oper_id,
g.oepr_date as oepr_date,
g.user_id,
g.oper_sql,
g.oper_status,
h.FILE_NAME,
'保留箱' as status,
h.file_format_id
from file_his_operate g, file_deled h
where g.file_id = h.file_id
and g.archive_id = h.archive_id
and h.status = 'N') aa
left join file_oper bb on aa.oper_id = bb.file_oper_id
left join user_sys cc on aa.user_id = cc.user_id
where 1 = 1
order by aa.oepr_date desc
select decode(status,'Y','审核','N','未审') as status,
file_name, file_id, archive_id, file_format_id from file_using
union all
select '退回' as status,
file_name, file_id, archive_id, file_format_id from file_returned
union all
select decode(status,'Y','废弃箱','N','保留箱') as status,
file_name, file_id, archive_id, file_format_id from file_deled
然后VFILE再与file_his_operate关联不管效率是否提高,我个人认为这样sql的结构比较清晰