学习笔记:concat(),concat_ws(),group_concat()

╰+哭是因爲堅強的太久メ 2023-02-26 02:06 5阅读 0赞

## concat() ##

拼接字符串，若任何一个参数为空，则返回null

## concat\_ws() ##

`concat_ws(separator,str1,str2)`,是concat()的特殊形式，第一个参数为其他参数见的分隔符，若separator=null,这函数返回null  
concat\_ws函数会忽略所有的null值，但不会忽略空字符串

## group\_concat() ##

用于将分组中的值连接组合

+----------+----+--------------------------+
    | locus    | id | journal                  |
    +----------+----+--------------------------+
    | AB086827 |  1 | Unpublished              |
    | AB086827 |  2 | Submitted (20-JUN-2002)  |
    | AF040764 | 23 | Unpublished              |
    | AF040764 | 24 | Submitted (31-DEC-1997)  |
    +----------+----+--------------------------+
    
    语句 SELECT locus,GROUP_CONCAT(id) FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus; 的返回结果为
    +----------+------------------+
    | locus    | GROUP_CONCAT(id) |
    +----------+------------------+
    | AB086827 | 1,2              |
    | AF040764 | 23,24            |
    +----------+------------------+
    
    语句 SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;的返回结果为
    +----------+----------------------------------------------------------+
    | locus    | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') |
    +----------+----------------------------------------------------------+
    | AB086827 | 2_1                                                      |
    | AF040764 | 24_23                                                    |
    +----------+----------------------------------------------------------+
    
    语句SELECT locus,GROUP_CONCAT(concat_ws(', ',id,journal) ORDER BY id DESC SEPARATOR '. ') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;的返回结果为
    +----------+--------------------------------------------------------------------------+
    | locus    | GROUP_CONCAT(concat_ws(', ',id,journal) ORDER BY id DESC SEPARATOR '. ') |
    +----------+--------------------------------------------------------------------------+
    | AB086827 | 2, Submitted (20-JUN-2002). 1, Unpublished                               |
    | AF040764 | 24, Submitted (31-DEC-1997) . 23, Unpublished                            |
    +----------+--------------------------------------------------------------------------+