测试了,知道了order by rownum的理由
SQL> with t as(select 1 a ,2 b from dual union all select 2,null from dual union all select 1.5,3 from dual)
2 select a,last_value(b ignore nulls)over() from t;
A LAST_VALUE(BIGNORENULLS)OVER()
---------- ------------------------------
1 3
2 3
1.5 3
已用时间: 00: 00: 00.04
SQL> with t as(select 1 a ,2 b from dual union all select 2,null from dual union all select 1.5,3 from dual)
2 select a,last_value(b ignore nulls)over(order by a) from t;
A LAST_VALUE(BIGNORENULLS)OVER(ORDERBYA)
---------- --------------------------------------
1 2
1.5 3
2 3
已用时间: 00: 00: 00.07
SQL> with t as(select 1 a ,2 b from dual union all select 2,null from dual union all select 1.5,3 from dual)
2 select a,last_value(b ignore nulls)over(order by rowid) from t;
select a,last_value(b ignore nulls)over(order by rowid) from t
*
第 2 行出现错误:
ORA-01446: 无法使用 DISTINCT, GROUP BY 等子句从视图中选择 ROWID 或采样
已用时间: 00: 00: 00.03
SQL> with t as(select 1 a ,2 b from dual union all select 2,null from dual union all select 1.5,3 from dual)
2 select a,last_value(b ignore nulls)over(order by rownum) from t;
A LAST_VALUE(BIGNORENULLS)OVER(ORDERBYROWNUM)
---------- -------------------------------------------
1 2
2 2
1.5 3