Mysql 多条件判断拼接字段数据

野性酷女 2023-02-12 05:26 163阅读 0赞

# Mysql 多条件判断拼接字段数据 #

# 示例 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FsaWNlX3FpeGlu_size_16_color_FFFFFF_t_70][]

先去重加上distinct

select distinct
    job_function_code,job_sub_function_code,job_fam_function_code from ps_inf_job_code
    order by job_function_code asc

去重结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FsaWNlX3FpeGlu_size_16_color_FFFFFF_t_70 1][]

想获取从后到前的path关系 并且获取最后一级数据作为code ，利用case when then else end 语句构造，when里可以写判断条件，表达式  then后可以是固定值，可以是拼接值，可以是字段值，参考下面示例及结果展示

select distinct
    'function'as cate_code0,
    'main_function'as cate_code1,
     (CASE
     WHEN (job_sub_function_code != '' or job_sub_function_code != null)
              and (job_fam_function_code = null or job_fam_function_code = '')THEN 'sub_function'
    else '' END)as cate_code2,
     (CASE
     WHEN (job_fam_function_code != '' or job_fam_function_code != null)THEN 'fam_function'
    else '' END)as cate_code3,
    (CASE
     WHEN (job_sub_function_code != '' or job_sub_function_code != null)
              and (job_fam_function_code = null or job_fam_function_code = '')THEN CONCAT(job_function_code)
     WHEN job_fam_function_code != null or job_fam_function_code != '' THEN concat(job_function_code,'>',job_sub_function_code)
    END)as path,
    (CASE
     WHEN (job_function_code != '' or job_function_code != null)
              and (job_sub_function_code = null or job_sub_function_code = '')
              and (job_fam_function_code = null or job_fam_function_code = '')THEN job_function_code
     WHEN (job_sub_function_code != '' or job_sub_function_code != null)
              and (job_fam_function_code = null or job_fam_function_code = '')THEN job_sub_function_code
     WHEN job_fam_function_code != null or job_fam_function_code != '' THEN job_fam_function_code
    END)as code,
    job_function_code,job_sub_function_code,job_fam_function_code
     from ps_inf_job_code order by code asc

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FsaWNlX3FpeGlu_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FsaWNlX3FpeGlu_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200524151510692.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FsaWNlX3FpeGlu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FsaWNlX3FpeGlu_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200524151627260.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FsaWNlX3FpeGlu,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FsaWNlX3FpeGlu_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200524151306555.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FsaWNlX3FpeGlu,size_16,color_FFFFFF,t_70