select a.spbh ,a.spmch,a.shengccj,
c.kcshl as shl ,a.dw,
b.yanghulb as yanghffcsh
from spkfk a,stib..zhongy b,spkfjc c
where a.spid=b.spid and a.spid=c.spid and c.kcshl>0
and b.yanghulb <> '10天养护' and b.yanghulb <>'5天养护'
现在这个yanghulb这个字段有4个值,上面有2个还有 一般养护 和NULL
按理上面应该检索出不包含上面这2个字段的所有条目
但实际执行时只能检索出 一般养护的条目,不能检索出包含 NULL的条目
为什么呢?
c.kcshl as shl ,a.dw,
b.yanghulb as yanghffcsh
from spkfk a,stib..zhongy b,spkfjc c
where a.spid=b.spid and a.spid=c.spid and c.kcshl>0
and b.yanghulb <> '10天养护' and b.yanghulb <>'5天养护'
现在这个yanghulb这个字段有4个值,上面有2个还有 一般养护 和NULL
按理上面应该检索出不包含上面这2个字段的所有条目
但实际执行时只能检索出 一般养护的条目,不能检索出包含 NULL的条目
为什么呢?
PRINT 'TRUE'
NULL不等于任何值
当使用比较运算符时,NULL值已被排除
可以这样变通
isnull(b.yanghulb,'') <> '10天养护'
c.kcshl as shl ,a.dw,
b.yanghulb as yanghffcsh
from spkfk a,stib..zhongy b,spkfjc c
where a.spid=b.spid and a.spid=c.spid and c.kcshl>0
and b.yanghulb <> '10天养护' and b.yanghulb <>'5天养护'
and isnull(b.yanghulb,'')
加上一条and isnull(b.yanghulb,'') 试试
c.kcshl as shl ,a.dw,
b.yanghulb as yanghffcsh
from spkfk a,stib..zhongy b,spkfjc c
where a.spid=b.spid and a.spid=c.spid and c.kcshl>0-------and isnull(b.yanghulb,'') in('10天养护','5天养护')
and b.yanghulb <> '10天养护' and b.yanghulb <>'5天养护'
测试一下
c.kcshl as shl ,a.dw,
b.yanghulb as yanghffcsh
from spkfk a,stib..zhongy b,spkfjc c
where a.spid=b.spid and a.spid=c.spid and c.kcshl>0
and (b.yanghulb is null or (b.yanghulb <> '10天养护' and b.yanghulb <>'5天养护'))
空值
空 (NULL) 值表示数值未知。空值不同于空白或零值。没有两个相等的空值。比较两个空值或将空值与任何其它数值相比均返回未知,这是因为每个空值均为未知。
所以出不来,要出来null字段行
可加 is null条件
改为
(b.yanghulb <> '10天养护' and b.yanghulb <>'5天养护' or b.yanghulb is null)