mysql group_concat函数语法

发表mysql group_concat函数相关文章
一句话说明: 行转列,按行分组连接

按行分组连接.

完整的语法如下
    group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
   如下例子
    mysql> select * from aa;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | 10   |
    |    1 | 20   |
    |    1 | 20   |
    |    2 | 20   |
    |    3 | 200  |
    |    3 | 500  |
    +------+------+
   6 rows in set (0.00 sec)
   3.1 以id分组,把name字段的值打印在一行,逗号分隔(默认)
       mysql> select id,group_concat(name) from aa group by id;
        +------+--------------------+
        | id   | group_concat(name) |
        +------+--------------------+
        |    1 | 10,20,20           |
        |    2 | 20                 |
        |    3 | 200,500            |
        +------+--------------------+
       3 rows in set (0.00 sec)
   3.2 以id分组,把name字段的值打印在一行,分号分隔
        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                          |
        +------+----------------------------------+
       3 rows in set (0.00 sec)
   3.3 以id分组,把去冗余的name字段的值打印在一行,逗号分隔
        mysql> select id,group_concat(distinct name) from aa group by id;

        +------+-----------------------------+
        | id   | group_concat(distinct name) |
        +------+-----------------------------+
        |    1 | 10,20                       |
        |    2 | 20                          |
        |    3 | 200,500                     |
        +------+-----------------------------+
       3 rows in set (0.00 sec)
   3.4 以id分组,把name字段的值打印在一行,逗号分隔,以name排倒序
        mysql> select id,group_concat(name order by name desc) from aa group by id;
        +------+---------------------------------------+
        | id   | group_concat(name order by name desc) |
        +------+---------------------------------------+
        |    1 | 20,20,10                              |
        |    2 | 20                                    |
        |    3 | 500,200                               |
        +------+---------------------------------------+
       3 rows in set (0.00 sec)


欢迎转载,转载请注明来自一手册:http://yishouce.com/mysql/func/group_concat

与mysql group_concat用法相关的文章

友情链接It题库(ittiku.com)| 版权归yishouce.com所有| 友链等可联系 admin#yishouce.com|粤ICP备16001685号-1