Question

mysql 如果查询的值为空，让某个数据赋值为0

SELECT
b.ED_value-a.ED_value
AS c
FROM
(SELECT * FROM t_data WHERE ED_time <= '2013-10-01' AND
id = 1 ORDER BY ED_time DESC LIMIT 1
) a,
(SELECT * FROM t_data WHERE ED_time <= '2013-10-04' AND
id = 1 ORDER BY ED_time DESC LIMIT 1
) b
WHERE a.id = b.id

首先b.ed_value是大于a.ed_value的值的，假如a.ed_value的值为null时，如何让a.ed_value=0,从而结果变为查询b.ed_value,我写了一个case when;
即把第二行替换为

CASE
WHEN b.ED_value IS NULL THEN a.ED_value
ELSE a.ED_value-b.ED_value
END
但是当b.ed_value有值，而a.ed_value没有值时返回结果仍为null而不是预想中的b.ed_value;

Answer 1

用nvL(null,0)可以不

追问

这个是oracle的函数，这里用的是mysql

追答

那还是用case when 吧，安全点
case when 字段 is not null
then 字段
else 0
end 0;

Answer 2

结合PHP分步操作就很简单了，if(x="") mysql_query("update $table set x=NULL");
嘿嘿~