今天QA发现一个Bug,
查了原因后发现Oracle的ROW_NUMBER() 函数工作方式很怪.

当Order by 后面的值都是一样的,如下面的SQL语句, 数据库中所有的MDATE和MTIME都是相等的,
同一条记录当增加红色显示的条件和不加这个条件是生成的行号竟然不一样!Faint!

select * from (select ROW_NUMBER()OVER(ORDER BY MDATE DESC,MTIME DESC) as rid, mtime, muser,
mdate, id, sampledesc from TBLALERTSAMPLE where 1=1) where rid>=1 and RID <=20

2.
如下语句，如果不加红色部分在Toad中查询竟然会报错，FT!
select /*+ leading(TBLEC) */ row_number()OVER(ORDER BY ecode) as rid, mtime, mdate, muser, eattribute1, ecode,ecdesc
from TBLEC
where ECODE in ( select ECODE from TBLECG2EC where ECGCODE ='XSX')