select * from table where field1+ field2 <> null and field2='XX'
select * from table where field1 is not null and field2='XX'准确描述应该是:一个字段不为空且另一个为XX
create table #test ( Field1 varchar(10) null, Field2 varchar(10) null ) go insert into #test(Field1,Field2) select 'xx' as Field1, 'xx' as Field2 union select 'xx' as Field1, null as Field2 union select null as Field1, null as Field2 union select null as Field1, 'xx' as Field2 go select * from #test where Field1 = (case when Field2 is null then 'xx' else null end) or Field2 = (case when Field1 is null then 'xx' else null end) go drop table #test
select * from table where (field1 is null and field2='XX') or (field1='XX' and field2 is null)
sorry 应该是 select * from table where ( not field1 is null and field2='XX') or (field1='XX' and not field2 is null)
SELECT * FROM WHERE field1 = 'XX' AND IsNull(field1,'') <> ''
(
Field1 varchar(10) null,
Field2 varchar(10) null
)
go
insert into #test(Field1,Field2)
select 'xx' as Field1, 'xx' as Field2
union
select 'xx' as Field1, null as Field2
union
select null as Field1, null as Field2
union
select null as Field1, 'xx' as Field2
go
select * from #test
where Field1 = (case when Field2 is null then 'xx' else null end)
or Field2 = (case when Field1 is null then 'xx' else null end)
go
drop table #test
select * from table where ( not field1 is null and field2='XX') or (field1='XX' and not field2 is null)