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