【MySql】MySql中的字符串结果集拼接成一个长字符

类似于ORACLE中的Start with...Connect By功能，找了好久发现group_concat方法满足要求。

示例：

创建表脚本

create table STUDENT
(
  NAME   VARCHAR2(20),
  SCORCE VARCHAR2(20)
)；
insert into STUDENT (NAME, SCORCE)
values ('Zhang', '50');
insert into STUDENT (NAME, SCORCE)
values ('Zhang', '60');
insert into STUDENT (NAME, SCORCE)
values ('LI', '80');
insert into STUDENT (NAME, SCORCE)
values ('LI', '70');
commit;

   

Oracle:

SELECT NAME, LTRIM(MAX(SYS_CONNECT_BY_PATH(SCORCE, ',')), ',') SCORCES
  FROM (SELECT NAME,
               SCORCE,
               ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY SCORCE DESC) RN
          FROM STUDENT)
 START WITH RN = 1
CONNECT BY RN - 1 = PRIOR RN
       AND NAME = PRIOR NAME
 GROUP BY NAME

MySql：

select name, group_concat(score) scores

  from student

 group by name

 

结果：

    

      

点评：对于字段汇总这种，group_concat确实方便，连拼接的字符逗号都省掉了。

后面发现oracle中也有类似函数wm_concat。