MySQL 连接列值

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

问题:
将多列值作为一列返回。例如，要查询emp表，返回这样的结果集:

CLARK WORKS AS A MANAGER
KING WORKS AS A PRESIDENT
MILLER WORKS AS A CLERK

然后，要得到这些数据，它们来自两个不同的列，emp表中的ename和job列。

select ename,job
  from emp
where deptno = 10;

mysql> select ename,job
    ->   from emp
    -> where deptno = 10;
+--------+-----------+
| ename  | job       |
+--------+-----------+
| CLARK  | MANAGER   |
| KING   | PRESIDENT |
| MILLER | CLERK     |
+--------+-----------+
3 rows in set (0.00 sec)

解决方案:
使用mysql的concat连接函数

select concat(ename,' WORKS AS A',job) as msg
  from emp
where deptno = 10;

mysql> select concat(ename,' WORKS AS A',job) as msg
    ->   from emp
    -> where deptno = 10;
+--------------------------+
| msg                      |
+--------------------------+
| CLARK WORKS AS AMANAGER  |
| KING WORKS AS APRESIDENT |
| MILLER WORKS AS ACLERK   |
+--------------------------+
3 rows in set (0.00 sec)