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>
)
mysql CONCAT()函数拼接有NULL处理
最新推荐文章于 2024-04-04 04:22:55 发布