专栏首页SQL实现模拟 GROUP_CONCAT() 函数

模拟 GROUP_CONCAT() 函数

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() 需要注意两个地方:

  1. GROUP_CONCAT() 聚合的结果有长度限制,默认是 1024,要想支持更长的聚合结果,可以修改 group_concat_max_len 变量。
  2. 聚合的结果并不是有序的,比如对于 abc 三个值,聚合后不一定就是 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

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 找出分组中具有极值的行

    你可能也遇到过这种需求:找出每个部门入职最早的员工的信息;获取每个科目最高分的学生信息;获取用户最近一次的完整登录信息。

    白日梦想家
  • SQL 打印成绩单

    这是 HackerRank 上的一道中级难度的 SQL 挑战题,实际上考察的是动态排序。

    白日梦想家
  • SQL 将多列的数据转到一列

    如题。假设我们要把 emp 表中的 ename、job 和 sal 字段的值整合到一列中,每个员工的数据(按照 ename -> job -> sal 的顺序展...

    白日梦想家
  • Jenkins常见问题集锦(五)

    参考:不能精确到秒。Jenkins定时构建表达式分为5部分,第一位最小,为分钟,后续依次为小时、天、月、周(0和7都表示周日)。

    DevOps持续交付
  • 步骤5 - Orchestra从微服务提供商获得结果,再发送回WebSocket服务器

    和第二步骤相匹配,第五步也是接收数据,因此是inbound处理,通过后缀Response区分这是一个响应。找到对应的Web Shop的WebSocket服务器s...

    Jerry Wang
  • python第十四课--排序及自定义函数

    1.排序 特点: 1).升序:从小到大 2).降序:从大到小 课堂实现选择排序:参看老郭选择排序.py文件 2.函数:(方法/method) 自定义...

    hankleo
  • 在语法压缩图中的模拟计算(Data Structures and Algorithms)

    与[1]类似,我们提出了一种算法来计算标记节点和未标记边缘图中的查询模式的模拟。然而,我们的算法是在一个压缩的图语法上工作的,而不是在原始的图上。与[1]算法相...

    李欣颖6837176
  • mybatis show sql 打印 SQL 语句到控制台

    微风-- 轻许--
  • 翻译连载 | 附录 A:Transducing(上)-《JavaScript轻量级函数式编程》 |《你不知道的JS》姊妹篇

    原文地址:Functional-Light-JS 原文作者:Kyle Simpson-《You-Dont-Know-JS》作者 JavaScript 轻量级函数...

    iKcamp
  • [机器学习] 用KNN识别MNIST手写字符实战

    Hi, 好久不见,粉丝涨了不少,我要再不更新,估计要掉粉了,今天有时间把最近做的一些工作做个总结,我用KNN来识别MNIST手写字符,主要是代码部分,全部纯手写...

    用户1622570

扫码关注云+社区

领取腾讯云代金券