备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本
一.需求
将父记录的信息跟只记录中的数据放在一起。例如,显示每个员工的姓名及其经理的姓名。
返回的结果集:
+-----------------------+
| emps_and_mgrs |
+-----------------------+
| SMITH woks for FORD |
| ALLEN woks for BLAKE |
| WARD woks for BLAKE |
| JONES woks for KING |
| MARTIN woks for BLAKE |
| BLAKE woks for KING |
| CLARK woks for KING |
| SCOTT woks for JONES |
| TURNER woks for BLAKE |
| ADAMS woks for SCOTT |
| JAMES woks for BLAKE |
| FORD woks for JONES |
| MILLER woks for CLARK |
+-----------------------+
二.解决方案
将MGR和EMPNO 自联接EMP表，找到每个员工的经理。
然后根据MySQL提供的字符串联接函数，生成结果集中需要的字符串。
select concat(a.ename, ' woks for ',b.ename) as emps_and_mgrs
from emp a, emp b
where a.mgr = b.empno;
测试记录:
mysql> select concat(a.ename, ' woks for ',b.ename) as emps_and_mgrs
-> from emp a, emp b
-> where a.mgr = b.empno;
+-----------------------+
| emps_and_mgrs |
+-----------------------+
| SMITH woks for FORD |
| ALLEN woks for BLAKE |
| WARD woks for BLAKE |
| JONES woks for KING |
| MARTIN woks for BLAKE |
| BLAKE woks for KING |
| CLARK woks for KING |
| SCOTT woks for JONES |
| TURNER woks for BLAKE |
| ADAMS woks for SCOTT |
| JAMES woks for BLAKE |
| FORD woks for JONES |
| MILLER woks for CLARK |
+-----------------------+
13 rows in set (0.00 sec)
155-2731-8020