select * from abcd
where
a>0
and b<>'bbb'
and d<>'abc'
and b<>''
union
select * from abcd
where
a>0
and c<>'ccc'
and d<>'abc'
and c<>''
where
a>0
and b<>'bbb'
and d<>'abc'
and b<>''
union
select * from abcd
where
a>0
and c<>'ccc'
and d<>'abc'
and c<>''
where
a>0
and (b is null or b<>'bbb')
and (c is null or c<>'ccc')
and (b is null and c is not null or b is not null and c is null)
and d<>'abc'
and (b is null or b<>'bbb')
and (c is null or c<>'ccc')
and (isnull(b,c) is not null )
and (b is null or b<>'bbb')
and (c is null or c<>'ccc')
and (isnull(b,c) is not null )
and d<>'abc'
where
a>0 and
((b is not null and b<>'bbb') or (c is not null and c<>'ccc'))
and d<'abc'
and case when b is not null and b<>'bbb' and c is null then 1
when b is null and c is not null and c<>'ccc' then 1
else 0
end=1
and d<>'abc'
呵呵,写着玩,
楼主要求“b,c不同时为空或不为空。”,中海的答案好像有问题
and (
( b is not null and b<>'bbb' and c is null )
or (b is null and c is not null and c<>'ccc'
)
)
and d<>'abc'
如果以上的abcd表中b列关联到xyz表中的x列select a,b,c,d,y,z from abcd,xyz
where ……
union
……
……那么,如果b为空,c不为空,假设xyz中有3项,则查询结果会出现3条记录。现在需要显示的记录为:
a b c d y z
111 NULL 222 333 NULL NULl即只显示1条记录,b、y、z列仍然要出现但为空,该怎么写?
where b is not null and c is not null