ROW_NUM()的基礎(chǔ)用法

row_num()是對結(jié)果集添加的一個偽列,是先按某種條件查詢出結(jié)果集之后又添加上的一個列;所以它總是從1開始,因此在使用的過程中需要謹(jǐn)慎使用>,>=,=,between...and這些條件;如下語句,執(zhí)行此條語句你會發(fā)現(xiàn)就算表中有數(shù)據(jù),查出的結(jié)果集也是空,是因為當(dāng)取出第一條數(shù)據(jù),此時ROWNUM = 1,不滿足ROWNUM > 10的條件,一次舍去,再取出下一條數(shù)據(jù)此時ROWNUM還會被賦值為1,依次類推,取到最后也還是沒有.

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

同時你會發(fā)現(xiàn)下面這兩條返回的結(jié)果是相同的

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

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

如何解決上面出現(xiàn)的問題呢?

嵌套語句,在內(nèi)層語句中提前把虛擬列都放好,在外層在根據(jù)虛擬列條件查詢

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

ROW_NUM()的其他用法

分組去重,內(nèi)層使用over函數(shù)分組,外層使用"topFileId" = 1實現(xiàn)去重功能
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那些情況發(fā)生。另外還要注意：rownum不能以任何基表的名稱作為前綴