sql查询默认值和null做关联

89 阅读 0 评论 59 点赞

我是靠谱客的博主慈祥外套，最近开发中收集的这篇文章主要介绍sql查询默认值和null做关联，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

遇到sql查询，如果有值就显示，如果是空则显示默认值，关联表，关联字段出现null，解决方法加ifnull

例子：

SELECT
   fici.id,
   fici.vin,
   fici.car_series AS carSeries,
   fici.car_model AS carModel,
   fici.open_status AS openStatus,
   fici.apply_time AS applyTime,
   IFNULL(
       ficmp.min_pic_url,
       ficmp1.min_pic_url
   ) AS picUrl,
   IFNULL(
       ficmp.model_describe,
       ficmp1.model_describe
   ) AS modelDescribe,
   fici.gbook_activate_time AS gbookActivateTime,
   IFNULL(
       ficmp.series_name,
       ficmp1.series_name
   ) AS seriesName
FROM
   ftms_iov_car_info fici
LEFT JOIN ftms_iov_enterprise_user fie ON fici.enterprise_subcode = fie.enterprise_account
LEFT JOIN (
   SELECT
       ficm.model_name,
       fi.pic_url,
       fi.pic_color,
       fi.min_pic_url,
       ficm.model_describe,
       fics.series_name
   FROM
       ftms_iov_car_model AS ficm
   LEFT JOIN ftms_iov_car_model_pic AS fi ON fi.car_model_id = ficm.id
   LEFT JOIN ftms_iov_car_series AS fics ON ficm.series_id = fics.id
   AND fi.del_flag = 0
   AND ficm.del_flag = 0
   AND fi.default_flag = 0
   AND fics.del_flag = 0
) ficmp ON fici.car_series = ficmp.model_name
AND fici.car_color = ficmp.pic_color
LEFT JOIN (
   SELECT
       ficm1.model_name,
       fi1.pic_url,
       fi1.pic_color,
       fi1.min_pic_url,
       ficm1.model_describe,
       fics1.series_name
   FROM
       ftms_iov_car_model AS ficm1
   LEFT JOIN ftms_iov_car_model_pic AS fi1 ON fi1.car_model_id = ficm1.id
   LEFT JOIN ftms_iov_car_series AS fics1 ON ficm1.series_id = fics1.id
   AND fi1.del_flag = 0
   AND ficm1.del_flag = 0
   AND fi1.default_flag = 1
   AND fics1.del_flag = 0
) ficmp1 ON fici.car_series = ficmp1.model_name
AND ifnull(fici.car_color, '') = ifnull(ficmp1.pic_color, '')
WHERE
   fici.enterprise_code = '**********'
AND fici.car_type = 2
AND fici.del_flag = 0
AND fici.open_status IN (2)
ORDER BY
   fici.create_time DESC