select a.name from table a, (select sal from (select distinct sal from table order by sal desc) where rownum<=4 minus select sal from (select distinct sal from table order by sal desc) where rownum<=3) b where a.sal=b.sal /
下面的语句可以实现 select * from (SELECT ROWNUM sn,B.* FROM (select A.* from EMP A order by SAL desc) B ) c where c.sn=4
select * from emp where rownum = 4 order by sal;
以上的语句调试都不能通过 关键是 select * from emp order by desc where rownum<5 由于有order by 是一张虚表,不能在他之上再次查询
你的ORACLE是什么版本 我在ORACLE8.1.7上调试通过的!
这样试试! select x.name,y.sal from TABLE x, (select max(sal) sal from (select sal from TABLE where sal<( select max(sal) from (select sal from TABLE where sal<( select max(sal) from (select sal from TABLE where sal<(select max(sal) from TABLE))))))) y where x.sal=y.sal /
以上的语句调试都不能通过 关键是 select * from emp order by desc where rownum<5 由于有order by 是一张虚表,不能在他之上再次查询虚表不能再次查询吗? select * from emp where rownum<5 order by desc 不管emp是不是虚表,都应该没问题呀
(select sal from
(select distinct sal from table order by sal desc)
where rownum<=4
minus
select sal from
(select distinct sal from table order by sal desc)
where rownum<=3) b
where a.sal=b.sal
/
select * from
(SELECT ROWNUM sn,B.* FROM
(select A.* from EMP A order by SAL desc) B ) c where c.sn=4
关键是
select * from emp order by desc where rownum<5
由于有order by 是一张虚表,不能在他之上再次查询
我在ORACLE8.1.7上调试通过的!
select x.name,y.sal from TABLE x,
(select max(sal) sal from
(select sal from TABLE
where sal<(
select max(sal) from
(select sal from TABLE
where sal<(
select max(sal) from
(select sal from TABLE
where sal<(select max(sal) from TABLE))))))) y
where x.sal=y.sal
/
由于你用的是Oracle8.0.5,Oracle在这方面是有缺陷。
若是Oracle8.1.7按我的第一种方法就可以。
若是Oracle9i,就更方便。对于Oracle8.0.5你也可以用存储过程来实现。
关键是
select * from emp order by desc where rownum<5
由于有order by 是一张虚表,不能在他之上再次查询虚表不能再次查询吗?
select * from emp where rownum<5 order by desc
不管emp是不是虚表,都应该没问题呀