MySQL中group_concat函数
完整的语法如下:
group_concat([] 要连接的字段 [order by 排序字段] [separator '分隔符'])
基本查询:
select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | 10 |
| 1 | 20 |
| 1 | 20 |
| 2 | 20 |
| 3 | 200 |
| 3 | 500 |
+----+------+
6 rows in set (0.00 sec)
1、以id分组,把name字段的值打印在一行,逗号分隔(默认)
select id, group_concat(name) from student group by id;
+------+---------------------+
| id | group_concat(name) |
+------+---------------------+
| 1 | 10,20,20 |
| 2 | 20 |
| 3 | 200,500 |
+------+---------------------+
3 rows in set (0.00 sec)
2、以id分组,把name字段的值打印在一行,分号分隔
select id, group_concat(name separator ';') from student 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、以id分组,把去冗余的name字段的值打印在一行
select id, group_concat(distinct name) from student group by id;
+-------+------------------------------+
| id | group_concat(distinct name) |
+-------+------------------------------+
| 1 | 10,20 |
| 2 | 20 |
| 3 | 200,500 |
+-------+------------------------------+
3 rows in set (0.00 sec)
4、以id分组,把name字段的值打印在一行,分号分隔,以name排序
select id, group_concat(name order by name desc) from student 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)