select * from b
where exists(select 1 from a where a.sid = 3 and b.id = a.id and b.c1 = a.c1 and b.c2 = a.c2 and nvl(b.c3,0) = nvl(a.c3,0))
where exists(select 1 from a where a.sid = 3 and b.id = a.id and b.c1 = a.c1 and b.c2 = a.c2 and nvl(b.c3,0) = nvl(a.c3,0))
select a.id,b.c1,b.t1,b.c2,b.t2,b.c3,b.t3 from a,b where a.sid=3 and a.id=b.id
b.*
from
表a a,
表b b
where
nvl(a.c1,0)=nvl(b.c1,0)
and
nvl(a.c2,0)=nvl(b.c2,0)
and
nvl(a.c3,0)=nvl(b.c3,0)
and
a.sid=3
b.*
from
表a a,
表b b
where
nvl(a.c1,9999999)=nvl(b.c1,9999999)
and
nvl(a.c2,9999999)=nvl(b.c2,9999999)
and
nvl(a.c3,9999999)=nvl(b.c3,9999999)
and
a.sid=3
但是 再注意一下 sql语句的优化
a.id,b.*
from
表a a,
表b b
where
nvl(a.c1,0)=nvl(b.c1,0)
and
nvl(a.c2,0)=nvl(b.c2,0)
and
nvl(a.c3,0)=nvl(b.c3,0)
and
a.sid=3
id sid c1 c2 c3
1 3 4 10
2 3 4 10 8
3 2 5 7 表b(没有第一条记录)id c1 t1 c2 t2 c3 t3
2 4 aa 10 bb 8 cc
3 4 gg 6 mm结果id c1 t1 c2 t2 c3 t3
1 4 aa 10 bb
2 4 aa 10 bb 8 cc解决马上结贴!!!
谢谢!
a.c1=b.c1 and a.c2 = b.c2 and a.c3 = b.c3
union all
select a.id,b.c1,b.t1,b.c2,b.t2,null,null from a,b where a.sid=3 and
a.c1=b.c1 and a.c2 = b.c2 and a.c3 is null