MySQL的concat，concat_ws，group_concat

来源：互联网发布：淘宝客服售后面试问题编辑：程序博客网时间：2024/05/16 14:09

MySQL的concat函数可以连接一个或者多个字符串

 mysql> select concat('11','22','33');        +------------------------+        | concat('11','22','33') |        +------------------------+        | 112233                 |        +------------------------+

concat_ws函数, 表示concat with separator,即有分隔符的字符串连接

mysql> select concat_ws(',','11','22','33');        +-------------------------------+        | concat_ws(',','11','22','33') |        +-------------------------------+        | 11,22,33                      |        +-------------------------------+

group_concat可用来行转列

mysql> select * from aa;    +------+------+    | id   | name |    +------+------+    |    1 | 10   |    |    1 | 20   |    |    1 | 20   |    |    2 | 20   |    |    3 | 200  |    |    3 | 500  |    +------+------+

mysql> select id,group_concat(name) from aa group by id;        +------+--------------------+        | id   | group_concat(name) |        +------+--------------------+        |    1 | 10,20,20           |        |    2 | 20                 |        |    3 | 200,500            |        +------+--------------------+

mysql> select id,group_concat(name separator ';') from aa group by id;        +------+----------------------------------+        | id   | group_concat(name separator ';') |        +------+----------------------------------+        |    1 | 10;20;20                         |        |    2 | 20                               |        |    3 | 200;500                          |        +------+----------------------------------+