ROW_NUM()的基础用法

row_num()是对结果集添加的一个伪列,是先按某种条件查询出结果集之后又添加上的一个列;所以它总是从1开始,因此在使用的过程中需要谨慎使用>,>=,=,between...and这些条件;如下语句,执行此条语句你会发现就算表中有数据,查出的结果集也是空,是因为当取出第一条数据,此时ROWNUM = 1,不满足ROWNUM > 10的条件,一次舍去,再取出下一条数据此时ROWNUM还会被赋值为1,依次类推,取到最后也还是没有.

SELECT ROWNUM,t.KAIHGUIY FROM t WHERE ROWNUM >10

同时你会发现下面这两条返回的结果是相同的

SELECT ROWNUM,t.KAIHGUIY FROM t WHERE ROWNUM !=10

SELECT ROWNUM,t.KAIHGUIY FROM t WHERE ROWNUM <10

如何解决上面出现的问题呢?

嵌套语句,在内层语句中提前把虚拟列都放好,在外层在根据虚拟列条件查询

SELECT*FROM (SELECT ROWNUM AS rn,t.KAIHGUIY FROM t) WHERE rn> 100

ROW_NUM()的其他用法

分组去重,内层使用over函数分组,外层使用"topFileId" = 1实现去重功能
select 
    "t"."PRODUCT_ID", 
    "t"."PRODUCT_NAME"
  from (
    select 
      row_number() over (partition by "crd"."APPLY_NUMBER", "crd"."INPUT_IDCARD" order by "crd"."CREATE_TIME" desc) as topFileId, 
      "crd"."CREATE_TIME", 
      "crd"."PRODUCT_ID", 
      "crd"."PRODUCT_NAME", 
      "crd"."INPUT_IDCARD"
    from "crd"
    where "CRD_DATA_WARNING"."CREATE_TIME" between to_date('2021-01-05 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and to_date('2021-02-05 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
  ) "t"
  where "topFileId" = 1

ROWNUM 和 ROWID虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid 相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum那些情况发生。另外还要注意：rownum不能以任何基表的名称作为前缀