mysql - SQL如何使null或empty变为整数零

Question

这是我的查询

  SELECT dp.comment,
     dp.tjah,
     dp.tjih,
     dp.tjor
     FROM dp
     LEFT OUTER JOIN rd ON (dp.uid = rd.uid)
     LEFT OUTER JOIN ri ON (rd.uid = ri.uid)
     LEFT OUTER JOIN rh ON (ri.uid = rh.uid)
     LEFT OUTER JOIN dv ON (rh.name = dv.name)
     WHERE rd.report_datetime='2014-06-20'
     LIMIT 5

这是输出

comment             tjsb                       tjih                       tjor 
------------------  --------------------------  --------------------  ---------------------
HWDP 31 FT                                  16                (NULL)                 (NULL)
15 ft pup joint                         (NULL)                (NULL)                     22
drill pipe range 3                          24                     0                    593
drill collar 31 ft                      (NULL)                    15                 (NULL)
pony DC 10 ft                           (NULL)                (NULL)                      2

如您所见，有很多空值如何修改查询，使空值变为 0 或 0.00，并且它是整数或 double 值而不是字符串。

Answer 1

您可以使用合并:

SELECT dp.comment
     , coalesce(dp.total_joints_at_shore_base, 0)
     , coalesce(dp.total_joints_in_hole, 0)
     , coalesce(dp.total_joints_on_rig, 0)
<...>