php - 在 MySQL 中将一行的列连接为单个输出

Question

我有一个成员表，其中包含名字、中间名和姓氏的单独列:

id,firstname,middlename,lastname,role,photo

如何通过一次查询检索到完整的名称？我尝试使用 AS 关键字，但我不知道如何将这 3 列合并为一列:

SELECT id,lastname AS name FROM members ORDER BY role

例如，我想检索全名(即名字、中间名和姓氏)作为 name，当然每个值之间有一个空格。

Answer 1

使用CONCAT_WS( )

SELECT ID, CONCAT_WS(' ', firstname, middlename, lastname) AS Name
FROM   members 
ORDER  BY role

来自文档，

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.