浅析MySQL中concat以及group_concat的使用

说明：

本文中使用的例子均在下面的数据库表tt2下执行：

mysql> create table tt2(
    -> id int auto_increment ,
    -> name varchar(20),
    -> date datetime,
    -> sigin int not null,
    -> sex varchar(10) not null,
    -> score int ,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.55 sec)

mysql> show tables;
+--------------+
| Tables_in_xy |
+--------------+
| stu          |
| test1        |
| test3        |
| test5        |
| test6        |
| test7        |
| test8        |
| tt2          |
+--------------+
8 rows in set (0.00 sec)

mysql> insert into tt2 values(0,'小明',2016-04-22 15:25:33,1,1,0);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '15:25
:33,1,1,0)' at line 1
mysql> desc tt2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| date  | datetime    | YES  |     | NULL    |                |
| sigin | int(11)     | NO   |     | NULL    |                |
| sex   | varchar(10) | NO   |     | NULL    |                |
| score | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.05 sec)

mysql> insert into tt2 values(0,'小明','2016-04-22 15:25:33',1,1,0);
Query OK, 1 row affected (0.15 sec)

mysql> select*from tt2;
+----+------+---------------------+-------+-----+-------+
| id | name | date                | sigin | sex | score |
+----+------+---------------------+-------+-----+-------+
|  1 | 小明 | 2016-04-22 15:25:33 |     1 | 1   |     0 |
+----+------+---------------------+-------+-----+-------+
1 row in set (0.00 sec)

mysql> insert into tt2 values(0,'小王','2016-04-22 15:25:47',3,0,0),(0,'小丽','2
016-04-19 15:26:02',2,1,0),(0,'小王','2016-04-07 15:26:14',4,0,0),(0,'小明','201
6-04-11 15:26:40',4,1,0),(0,'小明','2016-04-04 15:25:54',2,0,0);
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select*from tt2;
+----+------+---------------------+-------+-----+-------+
| id | name | date                | sigin | sex | score |
+----+------+---------------------+-------+-----+-------+
|  1 | 小明 | 2016-04-22 15:25:33 |     1 | 1   |     0 |
|  2 | 小王 | 2016-04-22 15:25:47 |     3 | 0   |     0 |
|  3 | 小丽 | 2016-04-19 15:26:02 |     2 | 1   |     0 |
|  4 | 小王 | 2016-04-07 15:26:14 |     4 | 0   |     0 |
|  5 | 小明 | 2016-04-11 15:26:40 |     4 | 1   |     0 |
|  6 | 小明 | 2016-04-04 15:25:54 |     2 | 0   |     0 |
+----+------+---------------------+-------+-----+-------+
6 rows in set (0.01 sec)


mysql>  insert into tt2 values(0,'maryleo ','2017-07-31 12:43:59',0,1,60),(0,'na
ncysun','2017-07-31 12:43:59',0,1,60);
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select*from tt2;
+----+----------+---------------------+-------+-----+-------+
| id | name     | date                | sigin | sex | score |
+----+----------+---------------------+-------+-----+-------+
|  1 | 小明     | 2016-04-22 15:25:33 |     1 | 1   |     0 |
|  2 | 小王     | 2016-04-22 15:25:47 |     3 | 0   |     0 |
|  3 | 小丽     | 2016-04-19 15:26:02 |     2 | 1   |     0 |
|  4 | 小王     | 2016-04-07 15:26:14 |     4 | 0   |     0 |
|  5 | 小明     | 2016-04-11 15:26:40 |     4 | 1   |     0 |
|  6 | 小明     | 2016-04-04 15:25:54 |     2 | 0   |     0 |
|  7 |          | 2017-04-22 15:25:47 |     0 | 0   |     0 |
|  8 | maryleo  | 2017-07-31 12:43:59 |     0 | 1   |    60 |
|  9 | nancysun | 2017-07-31 12:43:59 |     0 | 1   |    60 |
+----+----------+---------------------+-------+-----+-------+
9 rows in set (0.00 sec)


mysql> select concat(id,name,score) as info from tt2;
+-------------+
| info        |
+-------------+
| 1小明0      |
| 2小王0      |
| 3小丽0      |
| 4小王0      |
| 5小明0      |
| 6小明0      |
| 7 0         |
| 8maryleo 60 |
| 9nancysun60 |
+-------------+
9 rows in set (0.00 sec)

mysql> select concat(id,',',name,',',score) as info from tt2;
+---------------+
| info          |
+---------------+
| 1,小明,0      |
| 2,小王,0      |
| 3,小丽,0      |
| 4,小王,0      |
| 5,小明,0      |
| 6,小明,0      |
| 7, ,0         |
| 8,maryleo ,60 |
| 9,nancysun,60 |
+---------------+
9 rows in set (0.00 sec)

mysql> select concat_ws(',',id,name,score) as info from tt2;
+---------------+
| info          |
+---------------+
| 1,小明,0      |
| 2,小王,0      |
| 3,小丽,0      |
| 4,小王,0      |
| 5,小明,0      |
| 6,小明,0      |
| 7, ,0         |
| 8,maryleo ,60 |
| 9,nancysun,60 |
+---------------+
9 rows in set (0.00 sec)

mysql> select concat_ws('_',id,name,score) as info from tt2;
+---------------+
| info          |
+---------------+
| 1_小明_0      |
| 2_小王_0      |
| 3_小丽_0      |
| 4_小王_0      |
| 5_小明_0      |
| 6_小明_0      |
| 7_ _0         |
| 8_maryleo _60 |
| 9_nancysun_60 |
+---------------+
9 rows in set (0.00 sec)

mysql> select concat_ws('null',id,name,score) as info from tt2;
+---------------------+
| info                |
+---------------------+
| 1null小明null0      |
| 2null小王null0      |
| 3null小丽null0      |
| 4null小王null0      |
| 5null小明null0      |
| 6null小明null0      |
| 7null null0         |
| 8nullmaryleo null60 |
| 9nullnancysunnull60 |
+---------------------+
9 rows in set (0.00 sec)

mysql> select concat_ws(null,id,name,score) as info from tt2;
+------+
| info |
+------+
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+
9 rows in set (0.00 sec)

mysql> select name,min(id) from tt2 ;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELEC
T list contains nonaggregated column 'xy.tt2.name'; this is incompatible with sq
l_mode=only_full_group_by
mysql> select name,min(id) from tt2 group by name ;
+----------+---------+
| name     | min(id) |
+----------+---------+
| 小明     |       1 |
| 小王     |       2 |
| 小丽     |       3 |
|          |       7 |
| maryleo  |       8 |
| nancysun |       9 |
+----------+---------+
6 rows in set (0.02 sec)

mysql> select name,id from tt2 order by name;
+----------+----+
| name     | id |
+----------+----+
|          |  7 |
| maryleo  |  8 |
| nancysun |  9 |
| 小丽     |  3 |
| 小明     |  1 |
| 小明     |  5 |
| 小明     |  6 |
| 小王     |  2 |
| 小王     |  4 |
+----------+----+
9 rows in set (0.02 sec)

mysql> select name,group_concat(id) from tt2 group by name;
+----------+------------------+
| name     | group_concat(id) |
+----------+------------------+
|          | 7                |
| maryleo  | 8                |
| nancysun | 9                |
| 小丽     | 3                |
| 小明     | 1,5,6            |
| 小王     | 2,4              |
+----------+------------------+
6 rows in set (0.02 sec)

mysql> select name,group_concat(id order by id desc separator '_') from tt2 grou
p by name;
+----------+-------------------------------------------------+
| name     | group_concat(id order by id desc separator '_') |
+----------+-------------------------------------------------+
|          | 7                                               |
| maryleo  | 8                                               |
| nancysun | 9                                               |
| 小丽     | 3                                               |
| 小明     | 6_5_1                                           |
| 小王     | 4_2                                             |
+----------+-------------------------------------------------+
6 rows in set (0.00 sec)

mysql> select name,group_concat(concat_ws('-',id,score)order by id) from tt2 gro
up by name;
+----------+--------------------------------------------------+
| name     | group_concat(concat_ws('-',id,score)order by id) |
+----------+--------------------------------------------------+
|          | 7-0                                              |
| maryleo  | 8-60                                             |
| nancysun | 9-60                                             |
| 小丽     | 3-0                                              |
| 小明     | 1-0,5-0,6-0                                      |
| 小王     | 2-0,4-0                                          |
+----------+--------------------------------------------------+
6 rows in set (0.00 sec)

mysql>

参考：https://blog.csdn.net/Mary19920410/article/details/76545053