mysql拼接函数_mysql拼接函数

1.repeat()函数

使用方法：

REPEAT(str,count)  str:字符串或者是对应的值；count：几个数值，就是前面字符串的个数。

例子一：mysql>select repeat('ab',3);

+-------------------------------+

| repeat('ab',3)                |

+-------------------------------+

| ababab                        |

+-------------------------------+

1 row in set (0.00 sec)

范例：mysql> select repeat(name,2) from table_name;

+-------------------------------+

| repeat(name,2)                |

+-------------------------------+

| aaaa                          |

| bbbb                          |

| cccc                          |

+-------------------------------+

3 row in set (0.00 sec)

解析：用来复制字符串,如下'ab'表示要复制的字符串，后面的数字，如3表示复制的份数。

2. 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)

以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)

以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)

以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)

以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)

3.RTrim()函数

select concat(RTrim(name),'(',RTrim(country),')') from table_name order by name;

例子一：

select concat(LTRIM('     werrrt  '),'(',RTRIM(2343),')');

+------+---------------------------------------------------+

| id   |concat(LTRIM('  werrrt  '),'(',TRIM(  2343  ),')') |

+------+---------------------------------------------------+

|1     |werrrt  (2343)                                     |

+------+---------------------------------------------------+

1 rows in set (0.00 sec)

解析：LTrim()去掉串左边的空格。

例子二：

select concat(RTRIM('     werrrt  '),'(',RTRIM(2343),')');

+------+---------------------------------------------------+

| id   |concat(LTRIM('    werrrt  '),'(',TRIM( 2343 ),')') |

+------+---------------------------------------------------+

|1     |   werrrt(2343)                                    |

+------+---------------------------------------------------+

1 rows in set (0.00 sec)

解析：RTrim()去掉串右边的空格。Trim()去掉串左右两边的空格。