背景
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
结果:空的
修改后
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
修改后图片
后面没有能完成我的需求,需求是:空为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
结果如图
总结:为了业务,有时候希望为空就设定某个值,大家可以举一反三