4

MySQL数据库的函数使用：使用字符串拼接函数实现MySQL查询结果的拼接

 2 years ago
source link: https://segmentfault.com/a/1190000040531709
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

GROUP_CONCAT

  • 适用于拼接多条数据相同列,需要使用分割符的字符串查询结果.默认使用逗号作为分隔符

  • 语法: 必须配合GROUP BY一起使用

    GROUP_CONCAT(字段)

GROUP_CONCAT(字段 separator "分隔符")

GROUP_CONCAT(DISTINCT 字段 ORDER BY 字段 SEPARATOR "分隔符")

  • 示例:

    SELECT 
  employeeNumber,
  firstName,
  lastName,
  GROUP_CONCAT(DISTINCT customername ORDER BY customerName)
FROM
  employees
INNER JOIN customers ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName,lastname

    CONCAT_WS

  • 适用于拼接一条数据不同列,需要使用分隔符的字符串查询结果,指定使用的分隔符

  • 语法:

    CONCAT_WS("分隔符",str1,str2,...)

  • 示例:

    SELECT 
  CONCAT_WS(';',o.user_code,o.user_name) 
FROM sys_user o 
WHERE id = 5201314

  • 注意: 如果要拼接的字符串中有null,不会返回为null的结果

    CONCAT

  • 适用于拼接一条数据不同列,不需要使用分隔符的字符串查询结果

  • 语法:

    CONCAT(str1,str2...)

  • 示例:

    SELECT 
  CONCAT(o.user_code,o.user_name) 
FROM sys_user o 
WHERE id = 5201314
  • 注意: 如果要拼接的字符串中有一个是null,那么返回的结果就是null

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK