2023年6月27日发(作者：)

mysql⼀对多逗号分隔使⽤的group_concat函数使⽤场景：有单⽤户多机构多部门的业务场景，现准备统计不同机构下的⼈员请假情况，要求多部门的话将部门名称已逗号分开展现在列表中使⽤⽰例：SELECT e.*, group_concat( _department_name SEPARATOR ',' ) AS deptNameFROM ssj_org_user_department eLEFT JOIN ssj_org_department f ON _department_id = _department_idGROUP BY _id, _idoracle与mysql的使⽤区别：mysql:select field1,group_concat(field2) as 'all' from table group by field1oracle 10g以上可⽤wm_concat 等价于mysql 的 group_concatselect field1,wm_concat(field2) all from table group by field1select 1,_concat(2) from table t group by 1

_CONCAT⽀持的字符串的最⼤长度是4000create table tab1(a varchar2(16),b varchar2(16));insert into tab1 values('a','1');insert into tab1 values('a','2');insert into tab1 values('a','3');insert into tab1 values('b','4');insert into tab1 values('b','5');commit;SELECT a ,_CONCAT(b)FROM tab1GROUP BY a;1 a 1,2,32 b 4,5create table tab1(a varchar2(16),b varchar2(16));insert into tab1 values('a','1');insert into tab1 values('a','2');insert into tab1 values('a','3');insert into tab1 values('b','4');insert into tab1 values('b','5');commit;SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ',')), ',') B FROM (SELECT A, B, row_number() over(PARTITION BY A ORDER BY A, B) M, (ROW_NUMBER() OVER(ORDER BY A, B) + (DENSE_RANK() OVER(ORDER BY A))) NUMID FROM (SELECT A, B FROM tab1))START WITH M = 1CONNECT BY NUMID - 1 = PRIOR NUMIDGROUP BY Aoracle9i上⾯测试通过。oracle数据库sql⽅法1：select (select distinct field1 from tablename) field1,(select field2||',' from tablenamewhere field2='1')||(select field2||',' from tablenamewhere field2='2')||(select field2 from tablenamewhere field2='3') allfrom dual⽅法2：select distinct field1 , MAX(decode(field2, '1',field2 || ',', NULL)) || MAX(decode(field2, '2', field2 || ',', NULL)) || MAX(decode(field2, '3', field2)) from tablename where field1='A'group by field1