背景

1、用mybatis查询(mysql)时,会使用多个double字段做相加,但是呢,其中几个字段有可能是null,会抛空指针。 2、多表或者单表,查出来某个字段为空,希望设置为0

解决方法

原生态sql

SELECT A.MTMM_TYPE, LABEL_TYPE, MANUFACTURER, ADDRESS, OPERATIVENORM, A.CREATE_TIME, B.MTMM_TYPE isEnergySaving 
FROM MTMM_MAKER_LIST A LEFT JOIN ENERGY_LIST B ON A.MTMM_TYPE=B.MTMM_TYPE 
order by UPDATE_TIME DESC 

结果:空的 image.png

修改后

SELECT A.MTMM_TYPE, LABEL_TYPE, MANUFACTURER, ADDRESS, OPERATIVENORM, A.CREATE_TIME, B.MTMM_TYPE isEnergySaving ,ifnull(B.MTMM_TYPE,0) as isEnergySaving
FROM MTMM_MAKER_LIST A LEFT JOIN ENERGY_LIST B ON A.MTMM_TYPE=B.MTMM_TYPE 
order by UPDATE_TIME DESC 

修改后图片 image.png

后面没有能完成我的需求,需求是:空为0,不为空则为1 所以又进一步优化

IF(B.MTMM_TYPE IS NOT NULL,'是','否') as isEnergySaving 

完整语句

SELECT A.MTMM_TYPE, LABEL_TYPE,  ADDRESS, OPERATIVENORM, A.CREATE_TIME,IF(B.MTMM_TYPE IS NOT NULL,'是','否') as isEnergySaving 
FROM MTMM_MAKER_LIST A LEFT JOIN ENERGY_LIST B ON A.MTMM_TYPE=B.MTMM_TYPE 
order by UPDATE_TIME DESC 

结果如图 image.png

总结:为了业务,有时候希望为空就设定某个值,大家可以举一反三