MYSQL---SQL查询之多字段拼接

SELECT
    a.id,
    a.nick_name nickname,
    a.dept_id deptid,
    a.create_time createtime,
    a.stu_num stunum,
    a.email,
    CONCAT_WS ('/',one.name,two.name,three.name,four.name,five.name) departName
FROM
    `tb_user_basic_info` a
    left join tb_user_dept five on a.dept_id=five.id
    left join tb_user_dept four on four.id = five.pid
    left join tb_user_dept three on three.id = four.pid
    left join tb_user_dept two on two.id = three.pid
    left join tb_user_dept one on one.id = two.pid

CONCAT_WS 可以指定多字段的分隔符，且可以筛出无数据的字段，只给有数据的字段拼接分隔符 “/”

如上述查询的结果就是：one.name/two.name/three.name/four.name/five.name.

如果one.name为空，则显示为：two.name/three.name/four.name/five.name