探索11G新特性:Listagg的威力
最编程
2024-08-08 12:10:02
...
[20111220]listagg 11G的新特性.txt
显示相关的数据在一行,找到一个站点,总结了N多方法:
www.oracle-base.com/articles/misc/StringAggregationTechniques.php
我第一个知道的是:
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM (SELECT deptno,
ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;
后来了解了wmsys.wm_concat函数:
SELECT deptno,wmsys.wm_concat(ename) AS employees FROM emp GROUP BY deptno;
现在11G加入了listagg,感觉这个分析函数比wm_concat灵活一点,就是可以定义分割符号。
Select deptno,listagg(ename,'!') within group (order by sal) enames from emp group by deptno ;
显示相关的数据在一行,找到一个站点,总结了N多方法:
www.oracle-base.com/articles/misc/StringAggregationTechniques.php
我第一个知道的是:
SELECT deptno,
LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
FROM (SELECT deptno,
ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp)
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;
后来了解了wmsys.wm_concat函数:
SELECT deptno,wmsys.wm_concat(ename) AS employees FROM emp GROUP BY deptno;
现在11G加入了listagg,感觉这个分析函数比wm_concat灵活一点,就是可以定义分割符号。
Select deptno,listagg(ename,'!') within group (order by sal) enames from emp group by deptno ;