今天在修改别的程序时,发现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;