初学oracle,碰到个奇怪的问题,为什么条件between 1 and 5有结果,between 2 and 5就没结果了,20分哦,谢谢
1 select rownum,ename,sal,job from (select * from scott.emp order by sal desc)
2* where rownum between 1 and 5
SQL> / ROWNUM ENAME SAL JOB
---------- ---------- ---------- ---------
1 KING 5000 PRESIDENT
2 SCOTT 3000 ANALYST
3 FORD 3000 ANALYST
4 JONES 2975 MANAGER
5 BLAKE 2850 MANAGERSQL> ed
已写入文件 afiedt.buf 1 select rownum,ename,sal,job from (select * from scott.emp order by sal desc)
2* where rownum between 2 and 5
SQL> /未选定行
1 select rownum,ename,sal,job from (select * from scott.emp order by sal desc)
2* where rownum between 1 and 5
SQL> / ROWNUM ENAME SAL JOB
---------- ---------- ---------- ---------
1 KING 5000 PRESIDENT
2 SCOTT 3000 ANALYST
3 FORD 3000 ANALYST
4 JONES 2975 MANAGER
5 BLAKE 2850 MANAGERSQL> ed
已写入文件 afiedt.buf 1 select rownum,ename,sal,job from (select * from scott.emp order by sal desc)
2* where rownum between 2 and 5
SQL> /未选定行
///
insert into request
select 'A','B' from dual
union all
select 'A','D' from dual
union all
select 'C','B' from dual
union all
select 'B','D' from dual;
///鎵ц鏌ヨ
SELECT code1,code2 FROM request
GROUP BY rownum,code1,code2 HAVING rownum BETWEEN 2 AND 4;
///杈撳嚭缁撴灉
A D
C B
B D
///
insert into request
select 'A','B' from dual
union all
select 'A','D' from dual
union all
select 'C','B' from dual
union all
select 'B','D' from dual;
///查询语句
SELECT code1,code2 FROM request
GROUP BY rownum,code1,code2 HAVING rownum BETWEEN 2 AND 4;
///执行结果
A D
C B
B D
select rn,ename,sal,job from (select scott.emp.*,rownum as rn from scott.emp order by sal desc)
where rnbetween 2 and 5
当然,楼上的方法应该也是可行的。