mysql CONCAT()函数拼接有NULL处理

最新推荐文章于 2024-04-04 04:22:55 发布

yudian0427

最新推荐文章于 2024-04-04 04:22:55 发布

阅读量278

点赞数

文章标签： mysql 数学建模数据库

本文链接：https://blog.csdn.net/houyudian/article/details/131397685

版权

 select labelId as categoryId,CONCAT(objName,'_',IFNULL(lv1Name,''),'_',IFNULL(lv2Name,''),'_',IFNULL(lv3Name,'')) as category
        FROM
        (
        SELECT o.id                                     AS objId,
        o.obj_name                               AS objName,
        m.id                                     AS labelId,
        c3.id                                    AS categoryId,
        c3.category_name                         AS categoryName,
        c1.category_level                        as categoryIds,
        c1.category_name                         as categoryNames,
        CASE
        WHEN c1.category_level = 3 THEN c1.id
        when c2.category_level = 3 then c2.id
        when c3.category_level = 3 then c3.id
        END                                  as lv3Id,
        CASE
        WHEN c1.category_level = 3 THEN c1.category_name
        when c2.category_level = 3 then c2.category_name
        when c3.category_level = 3 then c3.category_name
        END                                  as lv3Name,
        CASE
        WHEN c1.category_level = 2 THEN c1.id
        when c2.category_level = 2 then c2.id
        when c3.category_level = 2 then c3.id
        END                                 as lv2Id,
        CASE
        WHEN c1.category_level = 2 THEN c1.category_name
        when c2.category_level = 2 then c2.category_name
        when c3.category_level = 2 then c3.category_name
        END                                  as lv2Name,
        CASE

        WHEN c1.category_level = 1 THEN c1.id
        when c2.category_level = 1 then c2.id
        when c3.category_level = 1 then c3.id
        END                                  as lv1Id,
        CASE
        WHEN c1.category_level = 1 THEN c1.category_name
        when c2.category_level = 1 then c2.category_name
        when c3.category_level = 1 then c3.category_name
        END                                  as lv1Name

        FROM dataplat_form_tag_metadata m
        LEFT JOIN dataplat_form_tag_category_relation_metadata r ON m.id = r.meta_data_id
        LEFT JOIN dataplat_form_tag_category c1 ON r.category_id = c1.id
        LEFT JOIN dataplat_form_tag_object o ON o.id = c1.obj_id
        LEFT JOIN (SELECT id, category_level, parent_id, category_name FROM dataplat_form_tag_category) c2
        ON c1.parent_id = c2.id
        LEFT JOIN (SELECT id, category_level, category_name FROM dataplat_form_tag_category) c3 ON c2.parent_id = c3.id
        where m.effective_flag=1 and m.id in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item,jdbcType=VARCHAR}
        </foreach>

        )