使用场景:有单用户多机构多部门的业务场景,现准备统计不同机构下的人员请假情况,要求多部门的话将部门名称已逗号分开展现在列表中
使用示例:
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