MySQL的concat，concat_ws，group_concat

最新推荐文章于 2024-01-10 15:07:29 发布

hiaugust

最新推荐文章于 2024-01-10 15:07:29 发布

阅读量595

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/hiaugust/article/details/9261499

版权

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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                          |
        +------+----------------------------------+