` 三种状态:
0
1
null `
**查询 0 和 null **
方法一: 不使用 or isnull()
select a.status ,a.* from t_table a
where ISNULL(a.status,0)=0 and a.isDelete = '0'
结果返回 : null 0 的数据
方法二:使用 or
select a.Status ,a.* from t_table a
where a.isDelete = '0' and a.status = 0
or a.isDelete = '0' and a.status is null
结果返回 :null 0 的数据
方法三 : 使用case when 和 isnull()
select a.status
from t_table a
where case when a.status is null then ISNULL(a.status,0) else a.status end = 0
结果返回 :null 0 的数据
如果查询结果无返回值(查询不到数据),可用 case when + count() 进行处理
isnull((SELECT a.status FROM t_a a WHERE a.id = tc.id AND a.flag = 1),0)
SELECT a.status FROM t_a a WHERE a.id = tc.id AND a.flag = 1
上面这条sql 可能无数据返回,导致数据库会进行类型转换,可能半天也一直在查询中
优化处理:
select case when
(select count(status) FROM t_a a WHERE a.id = tc.id AND a.flag = 1) = 1
then (SELECT a.status FROM t_a a WHERE a.id = tc.id AND a.flag = 1 )
else 0
end
)
case when 用法
http://blog.csdn.net/jobjava/article/details/27104259
http://blog.csdn.net/love_java_cc/article/details/52234415
http://www.jb51.net/article/51544.htm