• Oracle函数listagg使用


    作用

    可以实现将多列记录聚合为一列记录,实现数据的压缩

     

    语法结构

    listagg(measure_expr,delimiter) within group ( order by order_by_clause);

    解释:measure_expr可以是基于任何列的表达式

      delimiter分隔符,默认为NULL

      order_by_clause决定了列值的拼接顺序

    举例

    普通函数,对工资进行排序,并按照逗号进行拼接

    1 SQL> select listagg(ename,',')within group(order by sal)name from emp;
    2 
    3 NAME
    4 ----------------------------------------------------------------------------------------------------
    5 SMITH,JAMES,ADAMS,MARTIN,WARD,MILLER,TURNER,ALLEN,CLARK,BLAKE,JONES,FORD,SCOTT,KING

    分组函数

    1 SQL> select deptno,listagg(ename,',')within group(order by sal)name from emp group by deptno;
    2 
    3     DEPTNO NAME
    4 ---------- ----------------------------------------------------------------------------------------------------
    5     10 MILLER,CLARK,KING
    6     20 SMITH,ADAMS,JONES,FORD,SCOTT
    7     30 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE

    分析函数

     1 SQL> select deptno,ename,sal,listagg(ename,',')within group(order by sal)over(partition by deptno)name from emp;
     2 
     3     DEPTNO ENAME             SAL NAME
     4 ---------- ---------- ---------- ----------------------------------------
     5         10 MILLER           1300 MILLER,CLARK,KING
     6         10 CLARK            2450 MILLER,CLARK,KING
     7         10 KING             5000 MILLER,CLARK,KING
     8         20 SMITH             800 SMITH,ADAMS,JONES,SCOTT,FORD
     9         20 ADAMS            1100 SMITH,ADAMS,JONES,SCOTT,FORD
    10         20 JONES            2975 SMITH,ADAMS,JONES,SCOTT,FORD
    11         20 SCOTT            3000 SMITH,ADAMS,JONES,SCOTT,FORD
    12         20 FORD             3000 SMITH,ADAMS,JONES,SCOTT,FORD
    13         30 JAMES             950 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
    14         30 MARTIN           1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
    15         30 WARD             1250 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
    16         30 TURNER           1500 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
    17         30 ALLEN            1600 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
    18         30 BLAKE            2850 JAMES,MARTIN,WARD,TURNER,ALLEN,BLAKE
    19 
    20 14 rows selected.
  • 相关阅读:
    linux(6)查看进程ps命令
    Python 基础03 序列
    Python 基础02 基本数据类型
    Python基础01 Hello World!
    Linux vi/vim
    Laravel 的HTTP控制器
    Laravel 下的伪造跨站请求保护 CSRF#
    Linux 磁盘管理
    Linux 用户he用户组管理
    Linxu 用户和用户组管理1
  • 原文地址:https://www.cnblogs.com/lgx5/p/13618157.html
Copyright © 2020-2023  润新知