目录前言一、ROWNUM结果集的行号伪列1. 基本特性2. 常用场景限制返回的行数3. 分页的正确写法结合 ROWNUM4. ROWNUM 的局限二、OVER 与 ROW_NUMBER()窗口函数中的行号1. 基本语法2. 典型例子各部门按工资排序从高到低编号3. 与 ROWNUM 的核心区别三、其它OVER 英文 在这里如何解析PARTITION BY 对应的英文是什么什么叫 分析函数为什又叫窗口函数为什么 WHERE ROWNUM 1 永远不会返回任何行Oracle 中 PARTITION BY 的作用是什么四、使用删除要求删除效果举例五、内连接自己的实际使用六、一些基本操作6.1.连接数据库6.3.查看数据库6.3.在同一个数据库连接里把工作目录切换到了 FREEPDB1 这个子数据库里前言在 Oracle 数据库中ROWNUM和OVER是用于不同目的但经常被混淆的两个概念。简单来说ROWNUM是一个伪列它在查询结果集返回时为每一行分配一个唯一的、递增的行号。OVER是分析函数窗口函数的一部分它与ROW_NUMBER()、RANK()等函数配合在保留所有原始行的基础上进行分组、排序和编号。下面详细拆解这两个概念。一、ROWNUM结果集的行号伪列1. 基本特性ROWNUM是 Oracle 特有的在查询返回行的过程中动态生成。第一行ROWNUM 1第二行ROWNUM 2依次类推。关键点ROWNUM是在WHERE条件过滤之后、ORDER BY排序之前分配的。也就是说如果用了ORDER BY排序会改变行的物理顺序但ROWNUM仍然是按照原始获取顺序分配的除非使用子查询。2. 常用场景限制返回的行数-- 返回前 5 行不等同于排序后的前 5 SELECT * FROM employees WHERE ROWNUM 5;注意WHERE ROWNUM 1永远不会返回任何行因为第一行被过滤后第二行不会变成第一行ROWNUM始终从 1 开始。所以要实现真正的分页需要嵌套子查询。3. 分页的正确写法结合ROWNUMSELECT * FROM ( SELECT e.*, ROWNUM rn FROM ( SELECT * FROM employees ORDER BY salary DESC ) e WHERE ROWNUM 20 ) WHERE rn 10;4.ROWNUM的局限无法直接实现“每组内排名”。与ORDER BY同时使用时容易出错因为先分配ROWNUM再排序。属于 Oracle 特有不兼容其他数据库如 PostgreSQL、MySQL 用LIMIT。二、OVER与ROW_NUMBER()窗口函数中的行号OVER是分析函数的子句用于定义窗口即数据分组和排序的范围。最常用的与行号相关的函数是ROW_NUMBER()。1. 基本语法ROW_NUMBER() OVER ( [PARTITION BY 列1, 列2...] ORDER BY 列3 [ASC|DESC] )PARTITION BY可选将数据分成独立的分组如部门、类别。ORDER BY在每个组内指定排序规则。ROW_NUMBER()为每个组内的行分配一个唯一的、从1开始的连续编号。2. 典型例子各部门按工资排序从高到低编号SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employees;结果每个部门内部工资最高的人rn1次高rn2等等。3. 与ROWNUM的核心区别特性ROWNUMROW_NUMBER() OVER分配时机在WHERE后、ORDER BY前在整个查询的最后阶段SELECT时计算分组支持无支持PARTITION BY分组内部编号是否唯一整个结果集唯一且递增每个分组内唯一不同分组编号独立顺序控制受限于执行顺序容易出错通过ORDER BY明确控制标准兼容Oracle 专有SQL:2003 标准多数数据库支持三、其它OVER 英文 在这里如何解析PARTITION BY 对应的英文是什么什么叫 分析函数为什又叫窗口函数为什么 WHERE ROWNUM 1 永远不会返回任何行-------Oracle 中 PARTITION BY 的作用是什么四、使用删除要求一条 SQL 语句不使用 WITH基于表中 DDD 字段实际存在的月份删除“按月份排序后第 13 个最新月份”之前的所有数据。如果不同月份总数不足 13 个则不删除任何数据。DELETE FROM your_table WHERE DDD ( SELECT mon FROM ( SELECT DISTINCT TRUNC(DDD, MM) AS mon, ROW_NUMBER() OVER (ORDER BY TRUNC(DDD, MM) DESC) AS rn FROM your_table ) WHERE rn 13 );SQL 中使用了 TRUNC(DDD, MM)这个函数会保留年份和月份例如 2025-05-01、2024-12-01因此按它降序排序时年份不同的月份会自动按时间顺序排列测试分类Pattern ID测试场景描述基本功能P1少于13个不同月份全保留基本功能P2恰好13个不同月份全保留基本功能P3多于13个不同月份连续无空月删除最早一个月边界值P4跨年连续超过13个月删除最早一个月边界值P5边界月份第13个月内有多条记录且第14个月也有多条整月原子删除空月/稀疏数据P6存在空月但总月份数少于13全保留空月/稀疏数据P7存在空月且总月份数多于13删除最早的实际月份异常/极端P8表中只有一个月的数据全保留异常/极端P9表为空无操作删除效果举例No.月份YYYY-MM记录条数是否删除12026-032保留22026-022保留32026-012保留42025-122保留52025-112保留62025-102保留72025-092保留82025-082保留92025-072保留102025-062保留112025-052保留122025-042保留132025-032保留142025-0210删除152025-0110删除162024-1210删除总数据条数13 × 2 3 × 10 26 30 56预期保留条数26No.1 No.13预期删除条数30No.14 No.16删除逻辑说明由于总共有 16 个不同月份排序后最新的 13 个月No.1~No.13保留剩下的 3 个月No.14~No.16全部删除。这与“保留最近 13 个有数据的月份”的规则一致。五、内连接自己的实际使用SQL 语句用于查询在指定日期2026/06/22处于有效期内的每个 ID同时获取该 ID 上一个时间段按 PERIOD_START 排序的前一条记录的价格。IDPERIOD_STARTPERIOD_ENDPRICE12026/01/012026/03/3110012026/04/012026/06/3015012026/07/012026/09/3020022026/01/012026/12/3130032026/01/012026/05/3140032026/06/012026/08/3145042026/06/012026/06/30500SELECT A.ID, A.price, B.price FROM XXXXX A INNER JOIN XXXXX B ON A.ID B.ID AND B.PERIOD_START ( SELECT MAX(C.PERIOD_START) FROM XXXXX C WHERE C.ID A.ID AND C.PERIOD_STARTA.PERIOD_START ) WHERE 2026/06/22 BETWEEN A.PERIOD_START AND A.PERIOD_END查询结果A.IDA.priceB.price11501003450400六、一些基本操作6.1.连接数据库sqlplus /as sysdba6.3.查看数据库show pdbs // PDBS Pluggable Databases可插拔数据库复数show pdbs--PDB$SEEDFREEPDB1对比维度PDB$SEEDFREEPDB1英文全称 / 术语解释Pluggable Database Seed可插拔数据库种子/模板$为 Oracle 系统内部对象的常见命名符号Free Pluggable Database 1免费版的第一个可插拔数据库特指Oracle Database Free版本本质系统模板库用户业务库创建方式安装时由系统自动创建安装时由系统自动创建主要用途作为创建新 PDB 的模板存储用户的应用数据和执行操作读写状态READ ONLY只读READ WRITE可读写可修改性不可以添加或修改任何对象可以进行所有的数据库操作数量每个 CDB有且仅有一个默认有一个但可创建多个常见版本所有多租户架构的版本常见于Oracle Database Free版本6.3.在同一个数据库连接里把工作目录切换到了 FREEPDB1 这个子数据库里ALERT SESSION SET CONTAINERFREEPDB16.4.查看表的修改时间SELECT object_name AS table_name, created, last_ddl_time FROM user_objects WHERE object_typeTABLE AND object_nameT_XXXXXX