模拟 GROUP_CONCAT() 函数

白日梦想家

发布于 2020-07-20 11:23:16

3970

发布于 2020-07-20 11:23:16

文章被收录于专栏：SQL实现SQL实现

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

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-06-05，如有侵权请联系 cloudcommunity@tencent.com 删除

数据分析