本文介绍了SQL中WMSYS.WM_CONCAT函数的使用，用于合并字段值。首先展示了如何按部门(deptno)聚合员工(ename)，然后讨论了在窗口函数中使用WM_CONCAT，按部门分组并保持排序，以及如何通过DISTINCT去除重复值，从而获取每个部门的唯一员工组合。
分组聚合函数WMSYS.WM_CONCAT
合并某一字段：
SQL> select WMSYS.WM_CONCAT(ename) from emp;
WMSYS.WM_CONCAT(ENAME)
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL 根据deptno,合并ename：
SQL> select deptno,WMSYS.WM_CONCAT(ename) from emp group by deptno;
DEPTNO WMSYS.WM_CONCAT(ENAME)
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
WMSYS.WM_CONCAT(目标字段)over(partition BY 分组 order by 排序)
SQL> select deptno,WMSYS.WM_CONCAT(ename) over (partition by deptno) from emp;
DEPTNO WMSYS.WM_CONCAT(ENAME)OVER(PAR
10 CLARK,KING,MILLER
10 CLARK,KING,MILLER
10 CLARK,KING,MILLER
20 JONES,FORD,ADAMS,SMITH,SCOTT
20 JONES,FORD,ADAMS,SMITH,SCOTT
20 JONES,FORD,ADAMS,SMITH,SCOTT
20 JONES,FORD,ADAMS,SMITH,SCOTT
20 JONES,FORD,ADAMS,SMITH,SCOTT
30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
14 rows selected
SQL> select distinct deptno,WMSYS.WM_CONCAT(ename) over (partition by deptno) from emp;
DEPTNO WMSYS.WM_CONCAT(ENAME)OVER(PAR
10 CLARK,KING,MILLER
30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN
20 JONES,FORD,ADAMS,SMITH,SCOTT
扫一扫
312
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
