GROUP_CONCAT报错解决

 有如下表

其中awardee和awardee_unit都是保存的json类型的字符串, awardee是多个人员id, awardee_unit是部门的全路径

查询时要注意转换 

需要将name拼接起来合并成一行,直接 GROUP_CONCAT 会报错

百度的大部分答案是修改数据库配置去掉严格模式,如果不方便修改数据库可以这样做

select a.id,a.award_name,a.project_name,d.dept_name as awardee_unit,a.awardee_time,
        (SELECT GROUP_CONCAT(DISTINCT name) FROM biz_employee WHERE FIND_IN_SET(id, REPLACE(TRIM(REGEXP_REPLACE(a.awardee, '\\[|\\]', '')) ,' ','')) ) awardee
        from biz_technology_awards a
        join biz_technology_awards_emp b on a.id = b.technology_awards_id
        join biz_employee c on b.emp_id = c.id
        join sys_dept d on d.dept_id = JSON_EXTRACT(a.awardee_unit, concat('$[', json_length(a.awardee_unit) - 1, ']'))
        where c.id = 1

获取数组第一个和最后一元素，可以优化上面的sql

SELECT JSON_EXTRACT(a.awardee_unit, '$[0]') firstVal,JSON_EXTRACT(a.awardee_unit, '$[last]') lastVal FROM biz_technology_awards a