SELECT * FROM (SELECT tb.*,ROW_NUMBER() OVER(ORDER BY 1) rn FROM tb) WHERE rn >= 80 AND rn < 90;2 4楼写法比较耗资源
采用rownum: select * from(select * from (select a1.*,rownum rn from (select * from table_name) a1) where rn>80)where rn<90; rownum一次只能用一下,不能连用两次(即不能写为:rownum>80 and rownum<90)
select * from tbName where rownum<=90 minus select * from tbName where rownum<=80
select * from (select * from tbName where rownum<90) where rownum<10上面的rownum 能用大于和等号??
上面的我错了··· rownum是伪列,代表已经查询到的记录的行序号。还未查到的记录是没有rownum的。 因此ROWNUM>=1 AND ROWNUM<=10是可以查到前10条记录但是不能证明 ROWNUM>=500 AND ROWNUM<=600可以查处500~600的501条记录。 ROWNUM>=1 AND ROWNUM<=10在逻辑上相当于ROWNUM<=10 正确的方法是: select * from (select Z_YXJK_DMDH.*, rownum as rid from Z_YXJK_DMDH where rownum<=600) where rid > 500 或者 select * from Z_YXJK_DMDH where rownum<=600 minus select * from Z_YXJK_DMDH where rownum<=500 在效率上推荐前一种
select ids,name from (select rownum as ids,name from tableName ) where ids > 80 and ids < 91;测试成功
select * from table where rownum<=90 minus select * from table where rownum <=80;这个是可以的。。
select * from (select t.*,rownum rn from 表名 t) t1 where t1.rn between 80 and 90测试通过版本,只要把表名修改一下就OK了
select t.*,ROWNUM r from 表 t WHERE ROWNUM <= 90)
WHERE r>80
select 字段名,rownum rn from tab
) where rn >= 80 and rn <= 90
select * from (select a.*,rownum rn from tb) t where t.rn between 80 and 90
select * From
(select t.*,row_number() over(order by 1) as rn From t)
where rn between 80 and 90;
select * from t where rownum<=90
minus
select * from t where rownum<80
SELECT * FROM
(SELECT tb.*,ROW_NUMBER() OVER(ORDER BY 1) rn FROM tb)
WHERE rn >= 80 AND rn < 90;2 4楼写法比较耗资源
rownum一次只能用一下,不能连用两次(即不能写为:rownum>80 and rownum<90)
minus
select * from tbName where rownum<=80
因此ROWNUM>=1 AND ROWNUM<=10是可以查到前10条记录但是不能证明
ROWNUM>=500 AND ROWNUM<=600可以查处500~600的501条记录。
ROWNUM>=1 AND ROWNUM<=10在逻辑上相当于ROWNUM<=10
正确的方法是:
select * from (select Z_YXJK_DMDH.*, rownum as rid from Z_YXJK_DMDH where rownum<=600) where rid > 500
或者
select * from Z_YXJK_DMDH where rownum<=600
minus
select * from Z_YXJK_DMDH where rownum<=500
在效率上推荐前一种