LISTAGG() whith group()

最新推荐文章于 2021-01-19 16:58:57 发布

cscy21287

最新推荐文章于 2021-01-19 16:58:57 发布

阅读量133

点赞数

文章标签：数据库

本文介绍了 Oracle 数据库中用于字符串聚合的 LISTAGG 函数，该函数自 Oracle 11g Release 2 版本引入。通过使用示例展示了如何利用 LISTAGG 对数据进行分组并按指定顺序连接字符串，同时对比了 WMSYS.WM_CONCAT 函数的用法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list.

Base Data:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

Desired Output:

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

You can also use function Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 WMSYS.WM_CONCAT()
for example:
Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 select id,val from idtable;

ID VAL
---------- --------------------
10 abc
10 abc
10 def
10 def
20 ghi
20 jkl
20 mno
20 mno

8 rows selected

SQL> commit;

Commit complete

SQL>
SQL> SELECT ID, WMSYS.WM_CONCAT(VAL) AS ENAMES
2 FROM IDTABLE
3 GROUP BY ID;

ID ENAMES
---------- --------------------------------------------------------------------------------
10 abc,abc,def,def
20 ghi,jkl,mno,mno

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26118480/viewspace-714198/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26118480/viewspace-714198/