MySQL 如何将多列组合为一并使用自定义字符串格式化？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3757723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to combine multiple columns as one and format with custom strings?
提问by Moak
SELECT id, <X> AS name FROM `table`
Basically <X>is a combination of
lastname + ', ' + firstname
基本上<X>是
姓 + ', ' + 名字的组合
example would be
例子是
id | name |
2 | Smith, Bob |
3 | Jones, Susy |
This is just an example, I don't really want to combine names so simple.
这只是一个例子，我真的不想把名字组合得这么简单。
回答by Daniel Vassallo
What about the CONCAT()function?
怎么样的CONCAT()功能？
SELECT id, CONCAT(lastname, ', ', firstname) AS name FROM `table`;
If you are going to concatenate many fields, you could also consider the CONCAT_WS()function, where the first argument is the separator for the rest of the arguments, which is added between the strings to be concatenated:
如果您要连接多个字段，您还可以考虑该CONCAT_WS()函数，其中第一个参数是其余参数的分隔符，它添加在要连接的字符串之间：
SELECT id,
CONCAT_WS(',', field_1, field_2, field_3, field_4) list
FROM `table`;
回答by Haim Evgi
回答by Prahlad
SELECT
CONCAT('https://example.com/estimation/create?pId=',task_p_id,'&estId=',task_est_id) as live_url,
CONCAT('http://stage.example.com/estimation/create?pId=',task_p_id,'&estId=',task_est_id) as stage_url
FROM `ls_task` LEFT JOIN `ls_estimation` ON est_id=task_est_id LEFT JOIN `ls_project` ON p_id=task_p_id limit 10
回答by PAdrian
You can use GROUP_CONCAT():
您可以使用GROUP_CONCAT()：
Example of getting all the column names of a table separated by comma:
获取以逗号分隔的表的所有列名的示例：
SELECT GROUP_CONCAT(c.`COLUMN_NAME`) FROM information_schema.`COLUMNS` c
WHERE c.`TABLE_SCHEMA` = "DB_NAME" AND c.`TABLE_NAME`="TABLE_NAME"
Output:
输出：
column_name_1,column_name_2,column_name_3,column_name_4,...
