MySQL 提供了 GROUP_CONCAT()
函数,可以很方便地针对某字段下的值聚合成一个字符串,字符串内部默认使用“,”分割。
比如,我们要将 emp
表中每个部门的员工的姓名聚合到一起,就这么写 SQL :
SELECT
deptno,
GROUP_CONCAT(ename) AS enames
FROM
emp
GROUP BY deptno
聚合到一起的结果如下:
deptno enames
------ --------------------------------------
10 MILLER,KING,CLARK
20 FORD,ADAMS,SCOTT,JONES,SMITH
30 BLAKE,MARTIN,TURNER,WARD,JAMES,ALLEN
如果没有 GROUP_CONCAT()
函数,我们怎么实现聚合的效果呢?
答案是使用用户变量!使用用户变量可以做到看似将整张表的数据按行处理的效果。
为了让大家看清使用了用户变量的处理过程,我把实现的步骤拆分成两部分。
先来看部门中的员工姓名是怎么聚合到一块:
SELECT
deptno,
@ename := IF(
deptno = @deptno,
CONCAT_WS(',', @ename, ename),
ename
) AS enames,
@deptno := deptno
FROM
emp,
(SELECT
@deptno := NULL,
@ename := '') AS t
ORDER BY deptno
这步操作的输出如下:
deptno enames @deptno := deptno
------ ------------------------------- ------------
10 MILLER 10
10 MILLER,KING 10
10 MILLER,KING,CLARK 10
20 FORD 20
20 FORD,ADAMS 20
20 FORD,ADAMS,SCOTT 20
20 FORD,ADAMS,SCOTT,JONES 20
20 FORD,ADAMS,SCOTT,JONES,SMITH 20
30 BLAKE 30
30 BLAKE,MARTIN 30
30 BLAKE,MARTIN,TURNER 30
30 BLAKE,MARTIN,TURNER,WARD 30
30 BLAKE,MARTIN,TURNER,WARD,JAMES 30
30 BLAKE,MARTIN,TURNER,WARD,JAMES,ALLEN 30
从上面的结果可以看出,在每个分组里,员工姓名在不断组合,enames
也就越来越长。
最后,去掉中间过程生成的结果,只把最终的结果展现出来。
SELECT
deptno,
MAX(enames) AS enames
FROM
(SELECT
deptno,
@ename := IF(
deptno = @deptno,
CONCAT_WS(',', @ename, ename),
ename
) AS enames,
@deptno := deptno
FROM
emp,
(SELECT
@deptno := NULL,
@ename := '') AS t
ORDER BY deptno) t
GROUP BY deptno
在 SQL 里使用 CONCAT_WS()
函数将多个值拼接成一个字符串,最后使用 GROUP BY + MAX()
取出每个分组里面最长的字符串。
只是需要注意一个地方,用户变量 @ename
的初始值一定设置成 ''
,而不能是 NULL
,因为 NULL
和其它字符串拼接还是 NULL
。
另外,使用 GROUP_CONCAT()
需要注意两个地方:
GROUP_CONCAT()
聚合的结果有长度限制,默认是 1024,要想支持更长的聚合结果,可以修改 group_concat_max_len
变量。a
、b
、c
三个值,聚合后不一定就是 abc
。要保证有序可以设在 GROUP_CONCAT()
内部指定排序方式。比如,要想把最后的结果按照内部值的首字母进行顺序排序,就可以这么做:
SELECT
deptno,
GROUP_CONCAT(ename
ORDER BY ename) AS enames
FROM
emp
GROUP BY deptno
输出如下:
deptno enames
------ -------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
用户变量改一下排序字段也可以达到这种效果:
SELECT
deptno,
MAX(enames) AS enames
FROM
(SELECT
deptno,
@ename := IF(
deptno = @deptno,
CONCAT_WS(',', @ename, ename),
ename
) AS enames,
@deptno := deptno
FROM
emp,
(SELECT
@deptno := NULL,
@ename := '') AS t
ORDER BY deptno,ename) t
GROUP BY deptno