根据表中的行创建一个分隔列表

来源：互联网发布：一键救砖刷机软件编辑：程序博客网时间：2024/05/21 07:04
6.10 根据表中的行创建一个分隔列表---要求将emp表中的ename，用逗号间隔合并在一起显示。如：CLARK,KING,MILLERlistagg:把字符串连在一起SELECT deptno,       SUM(sal) AS total_sal,       listagg(ename, ',') within GROUP(ORDER BY ename) AS totoal_ename  FROM emp GROUP BY deptno;listagg(ename, ',')   第一个参数 第2个是分隔符(自定义)within GROUP(ORDER BY ename 当前分组内SQL>  SELECT empno,  2         ename,  3         deptno,  4         sal,  5         SUM(sal) over(PARTITION BY deptno) AS total_sal,  6         listagg(ename, ',') within GROUP(ORDER BY ename) over(PARTITION BY deptno) AS total_ename  7    FROM emp  8   WHERE deptno IN (10, 20);     EMPNO ENAME          DEPTNO        SAL  TOTAL_SAL TOTAL_ENAME---------- ---------- ---------- ---------- ---------- ------------------------------      7782 CLARK              10       2450       8750 CLARK,KING,MILLER      7839 KING               10       5000       8750 CLARK,KING,MILLER      7934 MILLER             10       1300       8750 CLARK,KING,MILLER      7876 ADAMS              20       1100      10875 ADAMS,FORD,JONES,SCOTT,SMITH      7902 FORD               20       3000      10875 ADAMS,FORD,JONES,SCOTT,SMITH      7566 JONES              20       2975      10875 ADAMS,FORD,JONES,SCOTT,SMITH      7788 SCOTT              20       3000      10875 ADAMS,FORD,JONES,SCOTT,SMITH      7369 SMITH              20        800      10875 ADAMS,FORD,JONES,SCOTT,SMITH已选择8行。主查询不分组的时候，分析函数取分组的数据
0 0