看到这个贴子,
很受启发,不过,让我想起另外一个更深一点的比如说有30条纪录,我想全部查询出来,但是其中的10条到20条之间用其中的某一个字段排序,其余的纪录不用排序,怎么做呢?
我想这样实现,是不是很麻烦呢?大家指点一下select * from table where rownum <= 10
union
select * from table where rownum <= 20 minus
select * from table where rownum < 10 order by name desc
union
select * from table where rownum > 20
很受启发,不过,让我想起另外一个更深一点的比如说有30条纪录,我想全部查询出来,但是其中的10条到20条之间用其中的某一个字段排序,其余的纪录不用排序,怎么做呢?
我想这样实现,是不是很麻烦呢?大家指点一下select * from table where rownum <= 10
union
select * from table where rownum <= 20 minus
select * from table where rownum < 10 order by name desc
union
select * from table where rownum > 20
下边的能执行,不过不知道为什么中间的那段为什么没有排序啊?请高人指点一下
能比这更简单的么?
select * from table where rownum <= 10
union
select * from (
select * from (
select * from table where rownum <= 20 minus
select * from table where rownum <= 10 ) order by id desc)
union
select * from (
select * from table where rownum <=30 minus
select * from table where rownum <=20 )
但是我不明白为什么要加上all???select * from table where rownum <= 10
union all
select * from (
select * from (
select * from table where rownum <= 20 minus
select * from table where rownum <= 10 ) order by to_number(id) desc)
union all
select * from(
select * from table where rownum <=30 minus
select * from table where rownum <=20 )
我这里边并没有重复的记录阿,每条记录都是唯一的
如果不用all, 只是中间那段数据不执行排序的操作
???
and id not in (select * from table1 where rownum<=10))
order by id
这条语句select * from table where rownum<=20
and id not in (select * from table1 where rownum<=10)
返回的是第11条到20 的记录select * from (select * from table where rownum<=20
and id not in (select * from table1 where rownum<=10))
order by id
上面的语句是排序的....
容易错的地方是先执行rownum后排序
http://expert.csdn.net/Expert/topic/2507/2507539.xml?temp=.6518061我自己写的,主要是参照TKPROF工具分析的结果来说明两种SQL语句的效率。顺便说明一点: 用MINUS确实省事,可是效率成问题,因为它会做两次全表
扫描(看看查询计划就一目了然)
SQL> select (*,rownum as con) from temp_table2 where con>=20;
select (*,rownum as con) from temp_table2 where con>=20
*
ERROR 位于第 1 行:
ORA-00936: 缺少表达式
where rownum>m-1 and rownum<m+m+1
from (select id,rownum row_num
from you_table
where rownum<=M) aa
where aa.row_num >=N;
from (select id,rownum row_num
from you_table
where rownum<=M
order by id desc) aa
where aa.row_num >=N
union
select id
from you_table;未测试,呵呵