首先说明由于需要我将数据库从mysql转到oracle
1 mysql的limit 1,25...是不是oracle的id not in(select id from tb where rownum<1) and rownum<=25
2 如果1成立的话,问题来了如下sql语句
①select value,id from zzb3 where isfirst =1 and parent_id=0 and dg_id in (691338928 , 693040866)order by value asc
②select value,id from zzb3 where isfirst =1 and parent_id=0 and dg_id in (691338928 , 693040866) and value not in(select value from zzb3 where rownum<1) and rownum<=25 order by value asc①的结果是:A001 A002 A003 ...A010 ...B001....
②的结果是:A001 A010 A011 A012...从1截取25个并且排序,哪里错了?
1 mysql的limit 1,25...是不是oracle的id not in(select id from tb where rownum<1) and rownum<=25
2 如果1成立的话,问题来了如下sql语句
①select value,id from zzb3 where isfirst =1 and parent_id=0 and dg_id in (691338928 , 693040866)order by value asc
②select value,id from zzb3 where isfirst =1 and parent_id=0 and dg_id in (691338928 , 693040866) and value not in(select value from zzb3 where rownum<1) and rownum<=25 order by value asc①的结果是:A001 A002 A003 ...A010 ...B001....
②的结果是:A001 A010 A011 A012...从1截取25个并且排序,哪里错了?
limit 1,25相当于选取1到25,1是开区间??mysql不熟悉,如果像你上面写的应该是开区间
(1,25)
你的原始mysql语句是什么??
oracle rownum用的不好结果就不对
COUNT(*)
----------
0
SQL> select count(*) from product where rownum> =1 and rownum<25;
COUNT(*)
----------
24
SQL> select count(*) from product where rownum<25;
COUNT(*)
----------
24
SQL> select count(*) from product where rownum>=1;
COUNT(*)
----------
532
SQL> select count(*) from product where rownum>=2;
COUNT(*)
----------
0--弄不好顺序乱,最好别这么做,可能是有序,但是打部分情况下无序
select rownum rn,product.* from product order by id;
--选取范围应该这么做
select *
from(
select rownum rn,a.*
from(
select * from product order by id
) a where rownum<20
) where rn >=10;--最好别这样做,看计划知道差别
select *
from(
select rownum rn,a.*
from(
select * from product order by id
) a
) where rn between 1 and 20;
最好找文档看看,oracle rownum陷阱还是不少的
product 里的数据都空?
比如select count(*) from product where rownum>=0.9; --ok
select count(*) from product where rownum<1.1 ---no rows returned
select count(*) from product where rownum<1.1 是有值的
在oracle里面用三层嵌套实现
select * from
(select rownum RW,ID,LAST_NAME,... from
(select * from s_emp order by id)) a
where a.RW between m and n;