mysql中GROUP BY结合GROUP_CONCAT的使用

我们知道，group by可以将sql查询结果按照group by后面列进行分类显示。比如：

select columnA,columnB from table group by columnA,columnB

 则查询结果将按照columnA和columnB分类显示。没有显示在group by中的列不能直接作为返回列放在sql语句中，比如如下sql就是不正确的

select columnA,columnC from table group by columnA 

 由于columnC不在group by的范围之类，所以这样写是不对的，所幸的是，group by支持一些sql 函数的使用，比如SUM,AVG,COUNT等等。这些都比较常用，今天我要记录下的是这个不常用的GROUP_CONCAT。

 

有一个需求，需要用到group by 才能实现，可是，我同是还需要返回某列的所有结果，（注意，不是做avg，sum等操作，我要枚举这列的所有结果），那么就可以用到GROUP_CONCAT。

举个例子：

我有一张数据库表结构如下：

列名	含义
year	年份
month	月份
volumn	期数

该表存储了某杂志的年份，月份和期数。如果需求对该表内容作如下显示：

 

2010年12月	第1期  第2期  第3期 第4期
2010年11月	第1期  第2期  第3期 第4期  第5期
2010年10月	第1期  第2期  第3期 第4期
2010年9月	第1期  第2期  第3期 第4期  第5期
2010年8月	第1期  第2期  第3期 第4期

 

sql该怎么写呢？按照年份和月份做group by？然后按照年份和月份做倒叙排列？

select year,month from magazine group by year,month order by year desc,month desc

 那具体的期数信息就丢了？能不能做group by的时候，还能返回在某个年份year和月份month分组下的所有期数volumn信息？（某个年份+月份下的期数信息是不固定的，只能通过数据库查询才能获得）

该是GROUP_CONCAT上阵的时候了。

select year,month GROUP_CONCAT(volumn) from magazine group by year,month order by year desc, month desc

 这样，查询的返回结果类似于：

year	month	GROUP_CONCAT(volumn)
2010	12	1,2,3,4
2010	11	1,2,3,4,5

 

不错吧？

还有点问题需要补充下，就是作为GROUP_CONCAT函数参数的字段，如过返回值为string，则上面的sql语句已经没有问题，但是如果是number，则返回的GROUP_CONCAT(volumn)值为BLOB类型（其实上面例子返回的就是一个blob类型，我只是为了演示的方便），需要做一下转化。

select year,month GROUP_CONCAT(conv( oct( volumn ) , 8, 10 )) from magazine group by year,month order by year desc, month desc

 上面的sql对volumn做了一个从8进制到10进制的转换，这样返回的就是一个字符串了。

mysql默认会以‘,’来分隔多的值，如果想用其他的分隔符来分隔返回结果，比如期望返回值是这样的：1|2|3|4

这可以用SEPARATOR来搞定。

select year,month GROUP_CONCAT(conv( oct( volumn ) , 8, 10 ) SEPARATOR '|') from magazine group by year,month order by year desc, month desc 

更牛的是，你甚至可以对返回的volumn进行排序！！

select year,month GROUP_CONCAT(conv( oct( volumn ) , 8, 10 ) order by volumn desc SEPARATOR '|') from magazine group by year,month order by year desc, month desc

 

这个不常用的东东，还是比较好用的。

 

评论

3 楼 sjzcmlt 2016-06-16  

谢谢，学习了！

2 楼 javeye 2010-07-05  

记录下：下面这样会交换两条记录的id。

update test t1,test t2 set t1.id=t2.id,t2.id=t1.id where t1.id=1 and t2.id=2

1 楼 javeye 2009-12-29  

在mysql中，GROUP_CONCAT函数的语法如下：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

以下内容转载于：http://www.cnblogs.com/Arlen/archive/2008/04/30/1177648.html
GROUP_CONCAT使用陷阱

1.int字段的连接陷阱
当你用group_concat的时候请注意，连接起来的字段如果是int型，一定要转换成char再拼起来，
否则在你执行后(ExecuteScalar或者其它任何执行SQL返回结果的方法)返回的将不是一个逗号隔开的串，
而是byte[]。

该问题当你在SQLyog等一些工具中是体现不出来的，所以很难发现。

select group_concat(ipaddress) from t_ip 返回逗号隔开的串
select group_concat(id) from t_ip 返回byte[]
select group_concat(CAST(id as char)) from t_dep 返回逗号隔开的串
select group_concat(Convert(id , char)) from t_dep 返回逗号隔开的串

附Cast,convert的用法：
CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)
CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。

这个类型 可以是以下值其中的一个：

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

2.长度陷阱
用group_concat连接字段的时候是有长度限制的，并不是有多少连多少。但你可以设置一下。

使用group_concat_max_len系统变量，你可以设置允许的最大长度。
程序中进行这项操作的语法如下，其中 val 是一个无符号整数：

SET [SESSION | GLOBAL] group_concat_max_len = val;

若已经设置了最大长度， 则结果被截至这个最大长度。

在SQLyog中执行 SET GLOBAL group_concat_max_len = 10 后，重新打开SQLyog，设置就会生效。

请注意，这种方式只是临时的，如要长久的修改，则需要修改mysql的配置节。

到my.cnf的mysqld节点下加上group_concat_max_len =99999……

重启mysql。