关于RowNum使用的一些实验

 

 

今天在修改别的程序时，发现SQL语句在应用RowNum的一些奇特的现象。经过了与同事的一番折腾后，终于搞清了其中的一些内容。

这里贴出来，与大家分享下过程……

--单表

select * from aa;

select * from aa WHERE ROWNUM<5;

SELECT * from bb WHERE ROWNUM<5;

--测试存在

SELECT * from ( SELECT * from aa WHERE EXISTS (SELECT * from bb WHERE aa.childid=bb.childid)) WHERE ROWNUM<5;--就近原则

SELECT * from ( SELECT * from aa  WHERE EXISTS (SELECT * from bb WHERE aa.childid=bb.childid ) AND ROWNUM<5);--同一级

SELECT * from ( SELECT * from aa WHERE EXISTS (SELECT * from bb WHERE aa.childid=bb.childid));

--Union测试rownum

SELECT childid,SUM(1) from (

SELECT aa.*,ROWNUM rn from aa WHERE ROWNUM<=10 

UNION ALL 

SELECT * from ( SELECT aa.*,ROWNUM rn from Aa WHERE ROWNUM<=20) aaa WHERE rn>10  

UNION ALL

SELECT * from ( SELECT aa.*,ROWNUM rn from Aa WHERE ROWNUM<=30) aaa WHERE rn>20  ) group BY childid

--连接测试

SELECT * from ( SELECT aa.*,ROWNUM rn from Aa WHERE ROWNUM<=15) aaa WHERE rn>5 ;

 SELECT * from aa WHERE ROWNUM<=10;

SELECT * from  (SELECT * from ( SELECT aa.*,ROWNUM rn from Aa WHERE ROWNUM<=15) aaa WHERE rn>5 ) a1,(SELECT * from aa WHERE ROWNUM<=10) a2 WHERE a1.childid=a2.childid(+);

SELECT * from  (SELECT * from ( SELECT aa.*,ROWNUM rn from Aa WHERE ROWNUM<=15) aaa WHERE rn>5 ) a1,(SELECT * from aa WHERE ROWNUM<=10) a2 WHERE a1.childid=a2.childid(+) AND ROWNUM<5;

SELECT * from  (SELECT * from ( SELECT aa.*,ROWNUM rn from Aa WHERE ROWNUM<=15) aaa WHERE rn>5 ) a1,(SELECT * from aa WHERE ROWNUM<=10) a2 WHERE a1.childid(+)=a2.childid;

SELECT * from  (SELECT * from ( SELECT aa.*,ROWNUM rn from Aa WHERE ROWNUM<=15) aaa WHERE rn>5 ) a1,(SELECT * from aa WHERE ROWNUM<=10) a2 WHERE a1.childid(+)=a2.childid AND ROWNUM<5;

--实验结果

SELECT *

  FROM (SELECT * FROM (SELECT a.*, rownum rn FROM m_sex A) where rn < 4)

 where rn > 1;