try:
select b.name2 as name,b.id2 as id,decode(sign(a.time1-b.time2),-1,b.time2,a.time1) as time,
decode(sign(a.time1-b.time2),-1,b.status2,status1) as status,
decode(sign(a.time1-b.time2),1,nvl(a.data1,''),'') as data1,
decode(sign(a.time1-b.time2),1,nvl(a.data12,''),'') as data12,
b.data2 as data3,
b.data21 as data4,
b.data22 as data5
from b, a
where b.name2 = a.name1(+)
and b.id2 = a.id1(+)
union all
select a.name1 as name,a.id1 as id,a.time1 as time,
a.status1 as status,a.data1 as data1,
a.data12 as data12,'' as data3,
'' as data4,'' as data5
from a, b
where a.name1 = b.name2(+)
and a.id1 = b.id2(+)
and b.name2 is null
select b.name2 as name,b.id2 as id,decode(sign(a.time1-b.time2),-1,b.time2,a.time1) as time,
decode(sign(a.time1-b.time2),-1,b.status2,status1) as status,
decode(sign(a.time1-b.time2),1,nvl(a.data1,''),'') as data1,
decode(sign(a.time1-b.time2),1,nvl(a.data12,''),'') as data12,
b.data2 as data3,
b.data21 as data4,
b.data22 as data5
from b, a
where b.name2 = a.name1(+)
and b.id2 = a.id1(+)
union all
select a.name1 as name,a.id1 as id,a.time1 as time,
a.status1 as status,a.data1 as data1,
a.data12 as data12,'' as data3,
'' as data4,'' as data5
from a, b
where a.name1 = b.name2(+)
and a.id1 = b.id2(+)
and b.name2 is null
decode(sign(a.time1-b.time2),-1,b.status2,status1) as status,
decode(sign(a.time1-b.time2),1,nvl(a.data1,0),0) as data1,
decode(sign(a.time1-b.time2),1,nvl(a.data12,0),0) as data12,
b.data2 as data3,
b.data21 as data4,
b.data22 as data5
from b, a
where b.name2 = a.name1(+)
and b.id2 = a.id1(+)
union all
select a.name1 as name,a.id1 as id,a.time1 as time,
a.status1 as status,a.data1 as data1,
a.data12 as data12,'' as data3,
'' as data4,'' as data5
from a, b
where a.name1 = b.name2(+)
and a.id1 = b.id2(+)
and b.name2 is null
特别是上面那个语句
select b.name2 as name, b.id2 as id,
decode(sign(a.time1-b.time2),-1,b.status2,status1) as status,
decode(sign(a.time1-b.time2),-1,b.time2,a.time1) as time,
decode(sign(a.time1-b.time2),1,nvl(a.data1,0),0) as data1,
decode(sign(a.time1-b.time2),1,nvl(a.data2,0),0) as data2,
b.data2 as data3, b.data21 as data4, b.data22 as data5
from b, a
where b.name2 = a.name1(+) and b.id2 = a.id1(+)union
select a.name1 as name, a.id1 as id, a.status1 as status,
a.time1 as time, a.data1 as data1,
a.data2 as data2, 0 as data3,
0 as data4, 0 as data5
from a, b
where a.name1 = b.name2(+) and a.id1 = b.id2(+)
and b.name2 is null发现,
x100 缺少了 time, status,
x1 缺少了 data1, data2
x67 缺少了 time, status
decode(sign(a.time1-b.time2),-1,b.status2,a.status1) as status,
decode(sign(a.time1-b.time2),-1,b.time2,a.time1) as time,
nvl(a.data1,0) as data1,
nvl(a.data2,0) as data2,
b.data2 as data3, b.data21 as data4, b.data22 as data5
from b, a
where b.name2 = a.name1(+) and b.id2 = a.id1(+)union
select a.name1 as name, a.id1 as id, a.status1 as status,
a.time1 as time, a.data1 as data1,
a.data2 as data2, 0 as data3,
0 as data4, 0 as data5
from a, b
where a.name1 = b.name2(+) and a.id1 = b.id2(+)
and b.name2 is null发现,x1 好了,但是仍然:
x100 缺少了 time, status,
x67 缺少了 time, status