mysql 一对多 逗号分隔 使用的group_concat函数

使用场景：有单用户多机构多部门的业务场景，现准备统计不同机构下的人员请假情况，要求多部门的话将部门名称已逗号分开展现在列表中

使用示例：

SELECT
    e.*, group_concat(
        f.org_department_name SEPARATOR ','
    ) AS deptName
FROM
    ssj_org_user_department e
LEFT JOIN ssj_org_department f ON e.org_department_id = f.org_department_id
GROUP BY
    e.org_id,
    e.user_id

oracle与mysql的使用区别：

mysql:

select field1,group_concat(field2) as 'all' from table group by field1

oracle 10g以上可用wm_concat 等价于mysql 的 group_concat
select field1,wm_concat(field2)  all from table group by field1
select t.filed1,wmsys.wm_concat(t.filed2)  from table t group by t.filed1 
WMSYS.WM_CONCAT支持的字符串的最大长度是4000



create 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 ,WMSYS.WM_CONCAT(b)
FROM tab1
GROUP BY  a;

1        a        1,2,3
2        b        4,5


create 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 = 1
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY A

oracle9i上面测试通过。



oracle数据库sql
方法1：
select (select distinct field1 from tablename) field1,(select  field2||','  from tablename
where  field2='1')||
(select  field2||',' from tablename
where  field2='2')||(select  field2  from tablename
where  field2='3') all
from 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