有一表(table1)
内容是:
ID 产品类别ID 产品编码 规格型号
1 A1 A001 16ml
2 null A002 8ml
3 null B001 5ml
4 B1 B002 6ml
5 A1 A003 3ml
...问:
我想select * from table1 where 产品类别ID='A1' and 产品编码='A002'时,只能够显示出来第2条记录?如何办?
就是说当产品类别ID为null时,就不判断了,表示全部类别(select * from table1 where 产品编码='A002'),不为null时才加上where 产品类别ID='A1'
内容是:
ID 产品类别ID 产品编码 规格型号
1 A1 A001 16ml
2 null A002 8ml
3 null B001 5ml
4 B1 B002 6ml
5 A1 A003 3ml
...问:
我想select * from table1 where 产品类别ID='A1' and 产品编码='A002'时,只能够显示出来第2条记录?如何办?
就是说当产品类别ID为null时,就不判断了,表示全部类别(select * from table1 where 产品编码='A002'),不为null时才加上where 产品类别ID='A1'
where (产品类别ID is null and 产品编码='A002') or
(产品类别ID='A1' and 产品编码='A002' and not exists(select 1 from table1 where 产品类别ID is null))
from table1
where (产品类别ID='A1' or 产品类别ID is null) and 产品编码='A002'
--或者(当产品类别ID中不存在的字符'All'时,如果存在就用其它值)
select * from table1 where isnull(产品类别ID,'All') in('All','A1') and 产品编码='A002'
来做下判断
select * from table1 where isnull(产品类别ID,'All') in('All','A1') and 产品编码='A002'
是不行的,因为会把 产品类别ID='All'的记录查出来
就是说当产品类别ID为null时,就不判断了,表示全部类别(select * from table1 where 产品编码='A002'),不为null时才加上where 产品类别ID='A1'
”
select * from table1 where 产品编码='A002' and 产品类别ID='A1'
union all
select * from table1 where 产品编码='A002' and 产品类别ID is null
and not exists (
select 1 from table1
where 产品编码='A002'
and 产品类别ID is not null
)