表a
id key name
1 1 e
2 null f表b
id key name
1 1 s
2 2 m
3 3 d
===========================
如果表a的key不为空,则2表 inner join a.id=b.id and a.key=b.key如果表a的key为空, 则2表 inner join a.id=b.id and a.name=b.name
id key name
1 1 e
2 null f表b
id key name
1 1 s
2 2 m
3 3 d
===========================
如果表a的key不为空,则2表 inner join a.id=b.id and a.key=b.key如果表a的key为空, 则2表 inner join a.id=b.id and a.name=b.name
union
select * from a inner join b on a.id=b.id and a.name=b.name where a.key is null
这样行不行的?:)
declare @b table(id int,[key] int,name varchar(1))insert into @a values(1,1,'e')
insert into @a values(2,null,'m')
insert into @a values(3,null,'e')
insert into @b values(1,1,'l')
insert into @b values(2,2,'k')
insert into @b values(3,3,'e')
insert into @b values(4,4,'v')select * from @a a inner join @b b
on a.id=b.id and (a.[key]=b.[key] or (a.[key] is null and a.name=b.name)) id key name id key name
----------- ----------- ---- ----------- ----------- ----
1 1 e 1 1 l
3 NULL e 3 3 e
@SQL='select...........join'
if select ....is null
@sql=@sql+'表B某子段'
else
@sql=@sql+'表B其他子段'
insert @ta
select 1, 1 , 'e' union all
select 2, null, 'f'declare @tb table(id int, [key] int, name varchar(2))
insert @tb
select 1, 1, 's' union all
select 2, 2, 'f' union all
select 3, 3, 'd'select ta.* from @ta ta inner join @tb tb on ta.id=tb.id
and ((ta.[key] is null and ta.[name]=tb.[name]) or ta.[key]=tb.[key])(所影响的行数为 2 行)
(所影响的行数为 3 行)id key name
----------- ----------- ----
1 1 e
2 NULL f(所影响的行数为 2 行)