复合语句的问题？select * from user where id not in (select top 15 id from user order by id) order by id "

[xieqi]

select * from user where id not in (select top 15 id from user order by id) order by id
这句sql在mssql里可以用，但在oracle里不能。
我改成select * from user where id not in (select id from user where rownum <=15 order by id) order by id
还是不行。

=================================================
select * from ctxsys.attribute where id not in (select id from ctxsys.attribute where rownum <= 3 order by id)order by id
*
ERROR 位于第 1 行:
ORA-00907: 缺少右括号

select * from ctxsys.attribute where id not in (select id from ctxsys.attribute where rownum <= 3 )order by id
这句可以，为什么order by id 加在rownum <= 3后面就不行呢？？

[beckhambobo]

select * from
(select a.*,row_number() over(order by id) rm from user a)
where rm>15;

[xieqi]

没看懂。
能select * from ctxsys.attribute where id not in (select id from ctxsys.attribute where rownum <= 3 )order by id
根据这句的表名帮我写一个吗？

[xieqi]

错了是
select * from ctxsys.attribute where id not in (select id from ctxsys.attribute where rownum <= 3 order by id)order by id
这句。

[xieqi]

有高手吗？

[tsj68]

oracle版本的问题.
根据你的错误提示,可以初步判定你的oracle是oracle8.05或以下的,不支持子查询排序.只有oracle8i及以上才支持.
beckhambobo的语句在oracle8i及以上是没问题的,只怕在你的oracle上没法运行.
要得到结果,必须分两不走.
1,创建临时表.
sql>create table temp as select * from ctxsys.attribute where 1=2;
2,插入排序的记录.
sql>insert into temp select * from ctxsys.attribute order by id desc;
3,删掉前15位记录.
sql>delete temp where rownum<16;
4.查询结果.
sql>select * from temp;

是不是太麻烦了,赶快升级到8i以上吧.

[xieqi]

我用的是oracle9.2.0.1.0

[xieqi]

谁能在oracle9.2.0.1.0调试出子查询排序，分不够在加。
我这个排序是用在分页程序上的，access里可以的,不能排序的话，分页还是能正常运行，但新加的记录总是在每页的最下面啊。我要倒过来。谁能解决？