案例一
在项目中, 需要对收藏数和投诉数进行统计, 条件是当前登录的用户, 写的SQL语句如下
select m.complainedCount, n.collectionCount from
(
select count(*) as complainedCount,create_by from tableA where order_status='complained' and create_by = '100'
) m
left join
(
select count(*) as collectionCount,create_by from tableB where create_by = '100'
) n
on m.create_by = n.create_by
执行结果如下
执行结果
但是实际上, collectionCount是2, 不是Null.
单独执行了每一个单独的SQL, 发现第一条SQL语句执行结果为空, 即: complainedCount = 0, create_by = Null, 这样和后面的表进行join时(m.create_by = n.create_by)就变成了 NULL = 100, 这样肯定是没有值的, 就变成了上图所示的collectionCount = Null的结果.
知道原因后, 对语句进行了改进, 如下:
select m.complainedCount, n.collectionCount from
(
select count(*) as complainedCount,'100' as create_by from tableA where order_status='complained' and create_by = '100'
) m
left join
(
select count(*) as collectionCount,'100' as create_by from tableB where create_by = '100'
) n
on m.create_by = n.create_by
这样即使当第一条或第二条单独的SQL语句有Null的值, 也会用100替代, 这样执行m.create_by = n.create_by时, 就能够正确匹配了.
执行结果显示正确了:
正确执行结果
案例二
项目中需要对两个值进行相减操作, 如下:
select (
(select charge_total from tableA where customer_id = '8') - (select SUM(amount) from tableB where create_by = '100')
) as restBillAmount from dual;
执行结果是restBillAmount为Null.
但实际上前面的表达式值是100, 后面的表达式结果是Null, 我以为结果会是100, 但实际上却是Null. 于是我写了几个SQL语句测试了一下, 如下:
select 100 - NULL from dual;
select NULL - 100 from dual;
发现结果都是Null. 那碰到如上的情况, 该如何解决呢? 答案是用IFNULL函数
select (
(select IFNULL(charge_total,0) from tableA where customer_id = '8') - (select IFNULL(SUM(amount),0) from tableB where create_by = '100')
) as restBillAmount from dual;
这样, 执行结果就是100了
总结
在mysql表设计之初, 就应该考虑默认值的情况, 像提到的这些情况, 多数是在创建表时, 写了default NULL, 这样在没值时, 数据库就自动填充了Null值. 实际上, 不应该全是Null, 有些可能默认是0, 1这些值.
当真的不可避免的有了Null值时, 要分析结果, 使用IFNULL等函数进行转