• 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.
  • 相关阅读:
    python正则表达式re模块
    frp+proxifier实现内网socks5反向代理
    新版kali 添加root权限用户,和字体颜色解决办法
    通达OA 11.6 RCE 漏洞(含EXP,POC,环境)
    水泽信息收集自动化工具(安装教程)
    宝塔面板phpmyadmin未授权访问漏洞
    greenplum窗口函数使用浅析
    执果索因调整greenplum table dk值
    greenplum分区表查看所占空间大小
    greenplum查看表的数据分布情况来调整dk值
  • 原文地址:https://www.cnblogs.com/lgx5/p/13618157.html
Copyright © 2020-2023  润新知