select
a.*,b.*
from
TABLE1 a,TABLE2 b
where
(case
when a.F1 is not null and b.F1 is not null
then case when a.F1=b.F1 then 1 else 0 end
when a.F2 is not null and b.F2 is not null
then case when a.F2=b.F2 then 1 else 0 end
when a.F3 is not null and b.F3 is not null
then case when a.F3=b.F3 then 1 else 0 end
when a.F4 is not null and b.F4 is not null
then case when a.F4=b.F4 then 1 else 0 end
else
0
end) = 1
a.*,b.*
from
TABLE1 a,TABLE2 b
where
(case
when a.F1 is not null and b.F1 is not null
then case when a.F1=b.F1 then 1 else 0 end
when a.F2 is not null and b.F2 is not null
then case when a.F2=b.F2 then 1 else 0 end
when a.F3 is not null and b.F3 is not null
then case when a.F3=b.F3 then 1 else 0 end
when a.F4 is not null and b.F4 is not null
then case when a.F4=b.F4 then 1 else 0 end
else
0
end) = 1
inner join TABLE2 b
on a.F1=IsNULL(b.F1,b.F2)
declare @TABLE1 table(F1 char(1),F2 char(1),F3 char(1),F4 char(1))
insert into @table1 select 'a','b','c','d'
insert into @table1 select 'e','g','g','e'declare @TABLE2 table(F1 char(1),F2 char(1),F3 char(1),F4 char(1))
insert into @table2 select 'a','c','d','e'
insert into @table2 select null,'g','d','f'--执行查询处理
select
a.*,b.*
from
@TABLE1 a,@TABLE2 b
where
(case
when a.F1 is not null and b.F1 is not null
then case when a.F1=b.F1 then 1 else 0 end
when a.F2 is not null and b.F2 is not null
then case when a.F2=b.F2 then 1 else 0 end
when a.F3 is not null and b.F3 is not null
then case when a.F3=b.F3 then 1 else 0 end
when a.F4 is not null and b.F4 is not null
then case when a.F4=b.F4 then 1 else 0 end
else
0
end) = 1--输出结果
/*
F1 F2 F3 F4 F1 F2 F3 F4
---- ---- ---- ---- ---- ---- ---- ----
a b c d a c d e
e g g e NULL g d f
*/
libin_ftsafe(子陌红尘) 赞一个!呵呵
a.*,b.*
from
TABLE1 a,TABLE2 b
where
(a.F1 is not null and b.F1 is not null and a.F1=b.F1)
or
((a.F1 is null or b.F1 is null)
and
a.F2 is not null and b.F2 is not null and a.F2=b.F2)
or
((a.F1 is null or b.F1 is null)
and
(a.F2 is null or b.F2 is null)
and
a.F3 is not null and b.F3 is not null and a.F3=b.F3)
or
((a.F1 is null or b.F1 is null)
and
(a.F2 is null or b.F2 is null)
and
(a.F3 is null or b.F3 is null)
and
a.F4 is not null and b.F4 is not null and a.F4=b.F4)
如果表为
TABLE1
F1 F2 F3 F4
a b c d
e g g e
g g g e --新加
TABLE2
F1 F2 F3 F4
a c d e
g d f
会把新加那行也select出来,要求的是党第一个为空第二个select条件才执行
F1 F2 F3 F4
a b c d
g g g e
e g g eTABLE2
F1 F2 F3 F4
a c d e
g d f
楼主的用意是记录的序号也要一一对应?即a b c d ==> a c d e
g g g e ==> g d f然后再根据F1--F4逐步匹配?
当记录A.F1不为空时select时匹配f1
当记录A.f1为空则匹配select时匹配F2 ....
按上表的话
结果还是为F1 F2 F3 F4 F1 F2 F3 F4
---- ---- ---- ---- ---- ---- ---- ----
a b c d a c d e
e g g e NULL g d f
当记录A.f1为空则匹配select时匹配F2 ....
------------------------------------------------------------------------------------------------------------
目前的情况是A.F1不为空,B.F1为空,应该如何匹配?按照楼主的描述,以上查询应该返回:
F1 F2 F3 F4 F1 F2 F3 F4
---- ---- ---- ---- ---- ---- ---- ----
a b c d a c d e
如果一个为空就进行下一项的匹配
A.F1不为空,B.F1为空
就用f2进行匹配
g g g e ==> g d f因为A表有两条记录F2字段为g,而B表中一条F1字段为空的记录的F2字段值为g,应该都可以匹配吧。
思维清晰,强!关注你!