在mysql中使用CONCAT内部CONCAT

Question

我有一个存储过程，其中一部分是：

SET @TableAlias = " ls_flock AS _flk ";
SET @Select = CONCAT(
    " _flk.Id as Id, _flk.Code as Code, ",
    CONCAT(" _em.FirstName", " ", "_em.LastName "), -- here I want to CONCAT 2 columns as one field.
    " as FlockManager"
  );
SET @Join = " JOIN employee as _em ON _flk.EmployeeId = _em.Id ";

我无法将两列作为一个带有CONCAT函数的字段连接起来。

如果删除下面一行，代码可以正常工作：

CONCAT(" _em.FirstName", " ", "_em.LastName "), -- here I want to CONCAT 2 columns as one field.
    " as FlockManager"

我得到了一个错误：

您的SQL语法出现了错误；请检查与MySQL服务器版本相对应的手册，以获得正确的语法，以使用“.LastName as FlockManager FROM ls_flock AS _flk _flk employee as _em ON _flk”。在第1行

Answer 1

我怀疑您希望生成一个使用CONCAT()的查询字符串，因为我看到表别名_em是在@Join变量中进一步声明的。为此，需要将函数移动到查询字符串中。

SET @Select = CONCAT(
    " _flk.Id as Id, ",
    "_flk.Code as Code, ",
    "CONCAT(_em.FirstName, "" "", _em.LastName) s FlockManager"
;

然后，你就不需要外部CONCAT()了

SET @Select = 
    " _flk.Id as Id, _flk.Code as Code, CONCAT(_em.FirstName, "" "", _em.LastName) as FlockManager";