mysql中group by 和 group_concat()的区别

Ethanhuyi

于 2016-11-24 16:18:57 发布

阅读量380

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/will5451/article/details/53321685

版权

mysql 专栏收录该内容

44 篇文章 0 订阅

订阅专栏

1，group_concat()

#例如以下sql查询语句

    root:dmz> select zone_id,group_concat(uin) from fd_sendprize_log group by zone_id;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------+
| zone_id | group_concat(uin)
                                                                                                                                                         |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------+
|       2 | 24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,
24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654,24456654 |
|       3 | 325223,325223,325223,325223,325223,325223,325223,325223,325223,325223,325223
                                                                                                                                                         |
|       6 | 878333223,878223,878223,878223,878223,878333223,878333223,878333223,878333223,878333223,878333223,878333223,878333223,878333223,878333223,878223
,878223,878223,878223,878223,878223,878223                                                                                                               |
|     101 | 878333223,878333223,878333223,878333223,878333223,878333223,878333223,878333223,878333223,878333223,2445223,2445223,2445223,2445223,2445223,2445
223,2445223,2445223,2445223,2445223,2445223,878333223                                                                                                    |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2,group by

#例如以下sql查询语句

root:dmz> select zone_id,uin from fd_sendprize_log group by zone_id;
+---------+----------+
| zone_id | uin      |
+---------+----------+
|       2 | 24456654 |
|       3 | 325223   |
|       6 | 878223   |
|     101 | 2445223 |
+---------+----------+

总结：以上可以看出group_concat()是可以查询某个队列下的全部信息并且默认是，逗号分隔的

而group by所查询出只是随机的一条分组信息

3，除此之外，还可以对这个组的值来进行排序再连接成字符串，例如按courses_id降序排：

select stu_id,group_concat(stu_id order by stu_id desc) as courses from stu_table where stu_id=2 group by stu_id;

------------------------------------------

| stu_id | courses |

------------------------------------------

| 2 | 5,4,3 |

------------------------------------------

4,需要注意的是,当连起来的是 int 型，一定要转换成 char 再拼起来

5，长度陷阱，可以使用 group_concat_max_len = val 设置最大的长度

在SQLyog中执行SET GLOBAL group_concat_max_len = val ,再重新打开生效