ORACLE 语句 新增Listagg函数（20201124）

2024-02-12 16:20
文章标签 oracle 函数 新增 语句 20201124 listagg

本文主要是介绍ORACLE 语句 新增Listagg函数（20201124），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1.【NOT】 Between A and B

当A<=n<=B时，返回true。

select * from TABLE_A where n between A and B;

2.【NOT】 Exists

当TABLE_A.ID在TABLE_B.ID中存在时，返回true。

select * from TABLE_A where exists(select 1 from TABLE_B where TABLE_A.ID=TABLE_B.ID);

3.ABS函数

返回n的绝对值。

select abs（n） from dual;

4.CEIL函数，FLOOR函数

ceil：天花板，向上取整，即返回大于或等于n的数值。

floor：地板，向下取整，返回小于或等于n的数值。

select ceil(-5.25),ceil(5.25) from dual;

结果：-5，6。

select floor(-5.25),floor(5.25) from dual;

结果：-6，5。

5.WM_CONCAT函数

常用于行数据合并，返回大对象数据类型CLOB，也可以通过TO_CHAR()转换，中间为逗号隔开。

select wm_concat(name) from sys_student where name like '季%'; 

但是此函数无法排序，需配合Partition分区分组，再使用Order by排序，

SELECT LOT.LOT_NO,
       TO_CHAR(wm_concat(SERIAL_NUMBER)
               over(partition by LOT_NO order by SERIAL_NUMBER)) SN
  FROM G_PALLET_LOT LOT, G_SN_STATUS SN
 WHERE LOT.PALLET_NO = SN.PALLET_NO
   AND LOT.PALLET_NO = '2K31D5814115';

 再嵌套一层Select From，取Max（SN）即可。

SELECT COUNT(*) QTY, LOT_NO, MAX(TO_CHAR(SN)) SN
  FROM (SELECT LOT.LOT_NO,
               TO_CHAR(wm_concat(SERIAL_NUMBER) over(partition by LOT_NO order by SERIAL_NUMBER)) SN
          FROM G_PALLET_LOT LOT, G_SN_STATUS SN
         WHERE LOT.PALLET_NO = SN.PALLET_NO
           AND LOT.PALLET_NO = '2K31D5814115')
 GROUP BY LOT_NO;

 oracle11g引进了listagg函数，也可满足排序要求，见第10点。

 若已该值为条件进行SQL查询，则需使用regexp_substr()结合connetc by，正则表达式截取查询

SELECT *
      FROM G_SN_STATUS
     WHERE WORK_ORDER IN
           (SELECT REGEXP_SUBSTR('23024251,123', '[^,]+', 1, rownum) AS WORK_ORDER
              FROM DUAL
            CONNECT BY ROWNUM <=
                       LENGTH('23024251,123') - LENGTH(REPLACE('23024251,123', ',', '')) + 1);

6. UNION，UNION ALL

union 和 union all的区别仅在于union合并去重，union all不去重。

表TABLE_A

ID   name   sex

1     张三     男

2     李四     男

3     陆六     女

表TABLE_B

ID   name   sex

1     王五     男

2     李四     男

3     陆六     女

4     赵八     男

select id,name,sex from table_a

union

select id,name,sex from table_b;

结果：

1     张三     男

1     王五     男

2     李四     男

3     陆六     女

4     赵八     男

select id,name,sex from table_a

union all

select id,name,sex from table_b;

结果：

1     张三     男

2     李四     男

3     陆六     女

1     王五     男

2     李四     男

3     陆六     女

4     赵八     男

7. intersect，minus

intersect计算交集，

minus计算差集（左边的记录在右边存在则删除，不存在则显示；右边的记录在左边不管存不存在都删除）。

select id,name,sex from table_a

intersect

select id,name,sex from table_b;

结果：

2     李四     男

3     陆六     女

select id,name,sex from table_a

minus

select id,name,sex from table_b;

结果：

1     张三     男

 8.新增、修改、删除列

新增tell，create_time列，默认系统日期。

alter table table_a add （tell number,create_time date default sysdate）;

修改tell列为tell_phone，并将字符类型修改为varchar2(13)。

alter table table_a rename column tell to tell_phone;

alter table table_a modify tell_phone varchar2(13);

删除tell_phone列。

alter table table_a drop column tell_phone;

 9.索引的添加，删除

为table_a.id添加索引。

create index id_idx on table_a(ID);

删除索引id_idx。

drop index id_idx;

 10.Listagg聚合函数

Listagg传两个参数，第一个为字段，第二个为分割的字符（不写直接拼接字符串）。

within group(order by xx)里面的就是聚合列拼接顺序的排序，用法跟order by 一样。

因为是聚合函数，所以需配合Group by使用。

SELECT LOT.LOT_NO,
       LISTAGG(SERIAL_NUMBER, ',') WITHIN GROUP(ORDER BY SERIAL_NUMBER) SN
  FROM G_PALLET_LOT LOT, G_SN_STATUS SN
 WHERE LOT.PALLET_NO = SN.PALLET_NO
   AND LOT.PALLET_NO = '2K31D5814115'
 GROUP BY LOT_NO;

 11.进制转换函数

1.10进制转16进制

select to_char(123,'fmXXX') from dual;

若位数长，多加X，过多会在左边多出空格，X前加fm即可。

2.16进制转10进制

select to_number('fff','XXX') from dual;

3.10进制转32进制
  --0~9，A-Z，不可用I、O、Q、U，序号不可重码
  FUNCTION TRANS10TO36_NOT_IOQU(IN_NUM NUMBER, OUT_LENGTH NUMBER)  
   RETURN VARCHAR AS
    RET VARCHAR(100);
    X   NUMBER;
    N   NUMBER;
    L   VARCHAR(32);
  BEGIN
    IF (INTEGER_NUM IS NULL) THEN
      IF (OUT_LENGTH IS NULL) THEN
        RETURN NULL;
      ELSE
        RETURN LPAD('0', OUT_LENGTH, '0');
      END IF;
    END IF;
    L := '0123456789ABCDEFGHJKLMNPRSTVWXYZ'; --不可用I、O、Q、U  完整就是36进制
    X := INTEGER_NUM;
    LOOP
      N   := MOD(X, 32); --mod(x,y) (1)MOD返回x除以y的余数,如果y是0,返回x。
      RET := CONCAT(SUBSTR(L, ABS(N) + 1, 1), RET);
      X   := TRUNC(X / 32);
      EXIT WHEN X = 0;
    END LOOP;
    IF (OUT_LENGTH IS NOT NULL AND LENGTH(RET) < OUT_LENGTH) THEN
      RET := LPAD(RET, OUT_LENGTH, '0');
    END IF;
    IF (INTEGER_NUM < 0) THEN
      RET := '-' || RET;
    END IF;
    RETURN RET;
  END;

4.特殊10进制转32进制
  --0~9999十进制 10000开始为999A，999B...ZZZZ
  FUNCTION NUM_TO_32(IN_SN VARCHAR2)  
   RETURN VARCHAR2 AS  
    STR     VARCHAR2(32) := '0123456789ABCDEFGHJKLMNPRSTVWXYZ'; --0~9 A~Z字符串去除I、O、Q、U 
    OUT_NUM VARCHAR2(25);
  BEGIN
  
    IF IN_SN = 'ZZZZ' THEN
      RETURN '0001';
    END IF;
  
    --32进制+1计算，根据获取SN的最后一码往后+1，例如：最后一码为A，则截取STR字符串A的后一位B
    IF SUBSTR(IN_SN, 4, 1) <> 'Z' THEN
      OUT_NUM := SUBSTR(IN_SN, 1, 3) ||
                 SUBSTR(STR, INSTR(STR, SUBSTR(IN_SN, 4, 1)) + 1, 1);
    ELSIF SUBSTR(IN_SN, 4, 1) = 'Z' AND SUBSTR(IN_SN, 3, 1) <> 'Z' THEN
    
      OUT_NUM := SUBSTR(IN_SN, 1, 2) ||
                 SUBSTR(STR, INSTR(STR, SUBSTR(IN_SN, 3, 1)) + 1, 1) || 0;
    
    ELSIF SUBSTR(IN_SN, 4, 1) = 'Z' AND SUBSTR(IN_SN, 3, 1) = 'Z' AND
          SUBSTR(IN_SN, 2, 1) <> 'Z' THEN
      OUT_NUM := SUBSTR(IN_SN, 1, 1) ||
                 SUBSTR(STR, INSTR(STR, SUBSTR(IN_SN, 2, 1)) + 1, 1) || '00';
    
    ELSIF SUBSTR(IN_SN, 4, 1) = 'Z' AND SUBSTR(IN_SN, 3, 1) = 'Z' AND
          SUBSTR(IN_SN, 2, 1) = 'Z' AND SUBSTR(IN_SN, 1, 1) <> 'Z' THEN
      OUT_NUM := SUBSTR(STR, INSTR(STR, SUBSTR(IN_SN, 1, 1)) + 1, 1) ||
                 '000';
    END IF;
  
    RETURN OUT_NUM;
  EXCEPTION
    WHEN OTHERS THEN
      OUT_NUM := 'MES_ERROR:' || SQLERRM;
      RETURN OUT_NUM;
  END;

5.数字转AAA-ZZZ算法

FUNCTION FUN_TO_AZ(IN_NUM NUMBER) RETURN VARCHAR2 AS
    STR VARCHAR2(25) := 'ABCDEFGHJKLMNPQRSTUVWXYZ'; --A~Z字符串去除I、O
  
    V_NUM    NUMBER;
    V_NUMBER VARCHAR(10);
    OUT_NUM  VARCHAR2(25);
  BEGIN
    V_NUM := IN_NUM;
  
    IF V_NUM < 0 OR V_NUM > (24 * 24 * 24 + 24 * 24 + 24) * 10000 THEN
      RETURN OUT_NUM;
    END IF;
    IF MOD(IN_NUM, 10000) = 0 THEN
      V_NUM := IN_NUM + 1;
    END IF;
    IF LENGTH(IN_NUM) < 4 THEN
      V_NUMBER := LPAD(V_NUM, 4, 0);
    ELSE
      V_NUMBER := LPAD(SUBSTR(V_NUM, -4, 4), 4, 0);
    END IF;
  
    V_NUM := TRUNC(V_NUM / 10000);
  
    --默认A开头,共24种组合
    IF V_NUM < 24 THEN
    
      OUT_NUM := SUBSTR(STR, V_NUM + 1, 1) || V_NUMBER;
    
      --AA-ZZ共24的2次方组合
    ELSIF V_NUM >= 24 AND V_NUM < (24 * 24 + 24) THEN
    
      OUT_NUM := SUBSTR(STR, TRUNC(V_NUM / 24), 1) ||
                 SUBSTR(STR, MOD(V_NUM, 24) + 1, 1) || V_NUMBER;
    
      --AAA-ZZZ共24的3次方组合
    ELSIF V_NUM >= (24 * 24 + 24) AND V_NUM < 24 * 24 * 24 THEN
    
      OUT_NUM := SUBSTR(STR, TRUNC((V_NUM / 24) / 24), 1) ||
                 SUBSTR(STR, MOD(TRUNC(V_NUM / 24), 24), 1) ||
                 SUBSTR(STR, MOD(V_NUM, 24) + 1, 1) || V_NUMBER;
    ELSE
      OUT_NUM := SUBSTR(STR, TRUNC((V_NUM / 24) / 24), 1) ||
                 SUBSTR(STR, MOD(TRUNC(V_NUM / 24), 24) + 1, 1) ||
                 SUBSTR(STR, MOD(V_NUM, 24) + 1, 1) || V_NUMBER;
    END IF;
  
    RETURN OUT_NUM;
  END;

12.误删记录以后，可查询1200s以内的数据，时间可设置

SELECT * FROM table as of timestamp(systimestamp - interval'1200'second)

这篇关于ORACLE 语句 新增Listagg函数（20201124）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/703023。

相关文章

hdu1171（母函数或多重背包）

hdu1171（母函数或多重背包）

题意：把物品分成两份，使得价值最接近 可以用背包，或者是母函数来解，母函数（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v） 其中指数为价值，每一项的数目为（该物品数+1）个 代码如下： #include<iostream>#include<algorithm>
阅读更多...
C++操作符重载实例(独立函数)

C++操作符重载实例(独立函数)

C++操作符重载实例，我们把坐标值CVector的加法进行重载，计算c3=c1+c2时，也就是计算x3=x1+x2，y3=y1+y2，今天我们以独立函数的方式重载操作符+（加号），以下是C++代码： c1802.cpp源代码： D:\YcjWork\CppTour>vim c1802.cpp #include <iostream>using namespace std;/*** 以独立函数
阅读更多...
函数式编程思想

函数式编程思想

我们经常会用到各种各样的编程思想，例如面向过程、面向对象。不过笔者在该博客简单介绍一下函数式编程思想. 如果对函数式编程思想进行概括，就是f(x) = na(x) , y=uf(x)…至于其他的编程思想，可能是y=a(x)+b(x)+c(x)…，也有可能是y=f(x)=f(x)/a + f(x)/b+f(x)/c… 面向过程的指令式编程 面向过程，简单理解就是y=a(x)+b(x)+c(x)
阅读更多...
Android13_SystemUI下拉框新增音量控制条

Android13_SystemUI下拉框新增音量控制条

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 Android13_SystemUI下拉框新增音量控制条 一、必备知识二、源码分析对比1.brightness模块分析对比2.statusbar/phone 对应模块对比对比初始化类声明对比构造方法 三、源码修改四、相关资源 一、必备知识 在Android12 版本上面已经完成了功能的实现，目前是在And
阅读更多...
Oracle type （自定义类型的使用）

Oracle type （自定义类型的使用）

oracle - type   type定义： oracle中自定义数据类型 oracle中有基本的数据类型，如number，varchar2，date，numeric，float....但有时候我们需要特殊的格式， 如将name定义为（firstname,lastname）的形式，我们想把这个作为一个表的一列看待，这时候就要我们自己定义一个数据类型 格式 ：create or repla
阅读更多...
ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

ORACLE 11g 创建数据库时 Enterprise Manager配置失败的解决办法 无法打开OEM的解决办法

在win7 64位系统下安装oracle11g，在使用Database configuration Assistant创建数据库时，在创建到85%的时候报错，错误如下： 解决办法： 在listener.ora中增加对BlueAeri-PC或ip地址的侦听，具体步骤如下： 1.启动Net Manager，在“监听程序”--Listener下添加一个地址，主机名写计
阅读更多...
Oracle Start With关键字

Oracle Start With关键字

Oracle Start With关键字 前言 旨在记录一些Oracle使用中遇到的各种各样的问题. 同时希望能帮到和我遇到同样问题的人. Start With (树查询) 问题描述: 在数据库中, 有一种比较常见得 设计模式, 层级结构 设计模式, 具体到 Oracle table中, 字段特点如下: ID, DSC, PID; 三个字段, 分别表示 当前标识的 ID(主键), DSC 当
阅读更多...
利用matlab bar函数绘制较为复杂的柱状图，并在图中进行适当标注

利用matlab bar函数绘制较为复杂的柱状图，并在图中进行适当标注

示例代码和结果如下：小疑问：如何自动选择合适的坐标位置对柱状图的数值大小进行标注？😂 clear; close all;x = 1:3;aa=[28.6321521955954 26.2453660695847 21.69102348512086.93747104431360 6.25442246899816 3.342835958564245.51365061796319 4.87
阅读更多...
OpenCV结构分析与形状描述符（11）椭圆拟合函数fitEllipse()的使用

OpenCV结构分析与形状描述符（11）椭圆拟合函数fitEllipse()的使用

操作系统：ubuntu22.04 OpenCV版本：OpenCV4.9 IDE:Visual Studio Code 编程语言：C++11 算法描述 围绕一组2D点拟合一个椭圆。 该函数计算出一个椭圆，该椭圆在最小二乘意义上最好地拟合一组2D点。它返回一个内切椭圆的旋转矩形。使用了由[90]描述的第一个算法。开发者应该注意，由于数据点靠近包含的 Mat 元素的边界，返回的椭圆/旋转矩形数据
阅读更多...
oracle分页和mysql分页

oracle分页和mysql分页

mysql 分页 --查前5 数据select * from table_name limit 0,5 select * from table_name limit 5 --limit关键字的用法：LIMIT [offset,] rows--offset指定要返回的第一行的偏移量，rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。   oracle 分页 --查前1-9
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2