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。

相关文章

C++中assign函数的使用

C++中assign函数的使用

《C++中assign函数的使用》在C++标准模板库中,std::list等容器都提供了assign成员函数,它比操作符更灵活,支持多种初始化方式,下面就来介绍一下assign的用法,具有一定的参考价... 目录​1.assign的基本功能​​语法​2. 具体用法示例​​​(1) 填充n个相同值​​(2)
阅读更多...
MySql基本查询之表的增删查改+聚合函数案例详解

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入（单行/多行及冲突处理）,SELECT实现数据检索（列选择、条件过滤、排序分页）,UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更
阅读更多...
PostgreSQL中rank()窗口函数实用指南与示例

PostgreSQL中rank()窗口函数实用指南与示例

《PostgreSQL中rank()窗口函数实用指南与示例》在数据分析和数据库管理中,经常需要对数据进行排名操作,PostgreSQL提供了强大的窗口函数rank(),可以方便地对结果集中的行进行排名... 目录一、rank()函数简介二、基础示例：部门内员工薪资排名示例数据排名查询三、高级应用示例1. 每
阅读更多...
全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

《全面掌握SQL中的DATEDIFF函数及用法最佳实践》本文解析DATEDIFF在不同数据库中的差异,强调其边界计算原理,探讨应用场景及陷阱,推荐根据需求选择TIMESTAMPDIFF或inte... 目录1. 核心概念：DATEDIFF 究竟在计算什么？2. 主流数据库中的 DATEDIFF 实现2.1
阅读更多...
MySQL中的LENGTH()函数用法详解与实例分析

MySQL中的LENGTH()函数用法详解与实例分析

《MySQL中的LENGTH()函数用法详解与实例分析》MySQLLENGTH()函数用于计算字符串的字节长度,区别于CHAR_LENGTH()的字符长度,适用于多字节字符集（如UTF-8）的数据验证... 目录1. LENGTH()函数的基本语法2. LENGTH()函数的返回值2.1 示例1：计算字符串
阅读更多...
MySQL 中的 CAST 函数详解及常见用法

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字
阅读更多...
Mysql实现范围分区表(新增、删除、重组、查看)

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型（范围、哈希、列表、键值）,主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区（Range Partitioning1、新建分区表：2、分
阅读更多...
MySQL 定时新增分区的实现示例

MySQL 定时新增分区的实现示例

《MySQL定时新增分区的实现示例》本文主要介绍了通过存储过程和定时任务实现MySQL分区的自动创建,解决大数据量下手动维护的繁琐问题,具有一定的参考价值,感兴趣的可以了解一下... mysql创建好分区之后，有时候会需要自动创建分区。比如，一些表数据量非常大，有些数据是热点数据，按照日期分区MululbU
阅读更多...
Python内置函数之classmethod函数使用详解

Python内置函数之classmethod函数使用详解

《Python内置函数之classmethod函数使用详解》：本文主要介绍Python内置函数之classmethod函数使用方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地... 目录1. 类方法定义与基本语法2. 类方法 vs 实例方法 vs 静态方法3. 核心特性与用法(1编程客
阅读更多...
Python函数作用域示例详解

Python函数作用域示例详解

《Python函数作用域示例详解》本文介绍了Python中的LEGB作用域规则,详细解析了变量查找的四个层级,通过具体代码示例,展示了各层级的变量访问规则和特性,对python函数作用域相关知识感兴趣... 目录一、LEGB 规则二、作用域实例2.1 局部作用域（Local）2.2 闭包作用域（Enclos
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2