mysql连接某列的值_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)