关于oracle 语句,in 的用法

题目

关于oracle 语句,in 的用法
我有一堆数据大概有几万条,按照时间排序的.现在我想取其中的8000条,取升序6000条,降序2000条.就是取头和尾的.但是我这样写报错
SELECT sbsfz,sbxm,zdbh,CJBH FROM cc_compare WHERE yhbz='06' AND vhdbz='0' AND cjzt='100' and zjlx='1' AND length(sbsfz)=18 and sbsfz in (SELECT sbsfz FROM cc_compare WHERE yhbz='06' AND vhdbz='0' AND cjzt='100' and zjlx='1' AND length(sbsfz)=18 AND ROWNUM < 6001 ORDER BY cjpc ) .其中在in 括号中得order by 会出错.不要ORDER BY cjpc 就不会报错,请问应该怎样写

答案

你的写法效率很低的
select * from
(SELECT sbsfz,sbxm,zdbh,CJBH
FROM cc_compare
WHERE yhbz='06' AND vhdbz='0' AND cjzt='100' and zjlx='1' AND length(sbsfz)=18 and
order by cjpc asc)
where rownum