select case when [b]sc_amount is null [/b]then 0 else sc_amount end from salechance;
select [b]isnull[/b](sc_amount,0) from salechance;
运行结果:
1000
0
700
6000
3000
7000
6700
25000
0
0
和
1000
700
6000
3000
7000
6700
25000
------------
前一个如果sc_amount 是空字符串和null,一样默认为null,
后一种如果sc_amount 是空字符串,不会默认为null
有待再去验证
-----------------------------------------
说明:
1,查询某字段为null语句 select * from rdrecords where iprice is null
2,查询某字段为null和为0的语句 select * from rdrecords where isnull(iprice,N'')=N'' 查询某字段为不为null且不为0的语句 select * from rdrecords where isnull(iprice,N'')<>N''
3,查询某字段,如果为null,就用0更新后的语句 select * from rdrecords where iprcie=isnull(iprice,0)
------------------------------
select sc_amount from salechance;
查询结果是
sc_amount
1000
700
6000
3000
7000
6700
25000
null
-----
select count(*) from salechance where isnull(sc_amount,'0')='0';
select count(*) from salechance where isnull(sc_amount,0)=0;
返回结果是:
1和7
说明什么呢?待查......
[color=red]结果发现
select * from salechance where sc_amount=0;
执行的是空字符串=0为true的,空字符串<>'0'[/color]
select * from salechance where isnull(sc_amount,'0')='0';
下面2句效果一样
select * from salechance where isnull(sc_amount,0)=0;
select * from salechance where isnull(sc_amount,'0')=0;
select [b]isnull[/b](sc_amount,0) from salechance;
运行结果:
1000
0
700
6000
3000
7000
6700
25000
0
0
和
1000
700
6000
3000
7000
6700
25000
------------
前一个如果sc_amount 是空字符串和null,一样默认为null,
后一种如果sc_amount 是空字符串,不会默认为null
有待再去验证
-----------------------------------------
说明:
1,查询某字段为null语句 select * from rdrecords where iprice is null
2,查询某字段为null和为0的语句 select * from rdrecords where isnull(iprice,N'')=N'' 查询某字段为不为null且不为0的语句 select * from rdrecords where isnull(iprice,N'')<>N''
3,查询某字段,如果为null,就用0更新后的语句 select * from rdrecords where iprcie=isnull(iprice,0)
------------------------------
select sc_amount from salechance;
查询结果是
sc_amount
1000
700
6000
3000
7000
6700
25000
null
-----
select count(*) from salechance where isnull(sc_amount,'0')='0';
select count(*) from salechance where isnull(sc_amount,0)=0;
返回结果是:
1和7
说明什么呢?待查......
[color=red]结果发现
select * from salechance where sc_amount=0;
执行的是空字符串=0为true的,空字符串<>'0'[/color]
select * from salechance where isnull(sc_amount,'0')='0';
下面2句效果一样
select * from salechance where isnull(sc_amount,0)=0;
select * from salechance where isnull(sc_amount,'0')=0;