我们知道,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
这个不常用的东东,还是比较好用的。
还有分隔符还可以自定义,默认是以“,”作为分隔符,若要改为“|||”,则使用SEPARATOR来指定,
例如:
mysql> SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR '|||') AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
除此之外,还可以对这个组的值来进行排序再连接成字符串,例如按courses_id降序来排:
mysql> SELECT student_id, GROUP_CONCAT(courses_id ORDER BY courses_id DESC) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
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,设置就会生效。