select * from
(select rownum() over(order by colname) rnum,t.* from t) tb
where tb.rnum between 11 and 20;or select * from
(select rank() over(order by colname) rnum,t.* from t) tb
where tb.rnum between 11 and 20;orselect * from
(select rownum rnum,tt.* from (select * from t order by colname) tt)
where rnum between 11 and 20;
(select rownum() over(order by colname) rnum,t.* from t) tb
where tb.rnum between 11 and 20;or select * from
(select rank() over(order by colname) rnum,t.* from t) tb
where tb.rnum between 11 and 20;orselect * from
(select rownum rnum,tt.* from (select * from t order by colname) tt)
where rnum between 11 and 20;
select * from
(select row_number() over(order by colname) rnum,t.* from t) tb
where tb.rnum between 11 and 20;
select * from (select 列1,列2 from 表A where 条件 order by 列1) where rownum <=1010到20条
select * from (select B.*,rownum as row_id from (select 列1,列2 from 表A where 条件 order by 列1) B where rownum <=20) and row_id >=10
呵呵,感谢大家的回答,但是不明白为什么这样写,大家能给我讲解一下么sql学的不深,望大家见谅
(select rownum rnum,tt.* from (select * from t order by colname) tt)
where rnum between 11 and 20;这条看明白了
行,这些语句我再好好看看还想问大家一个问题,如果数据比较多的时候语句select * from 表A; 和上面的只查一定数量的sql语句,哪个执行效率更快呢?
用分析函数 row_number(),rank()的效率好一些如果不排序
可以
select * from ( select rownum id,t.* from t where rownum<20) tt
where tt.id>10;效率最好。
我测试了一下:第一条:select * from
(select rownum() over(order by colname) rnum,t.* from t) tb
where tb.rnum between 11 and 20;
报错:未找到预期的FROM关键字第二条:select * from
(select rank() over(order by colname) rnum,t.* from t) tb
where tb.rnum between 11 and 20;
成功执行,执行时间:0.062第三条:select * from
(select rownum rnum,tt.* from (select * from t order by colname) tt)
where rnum between 11 and 20;
成功执行,执行时间:0.063第四条:select * from t
执行时间:0.187
select * from ( select rownum id,t.* from t where rownum<20 order by 列) tt
where tt.id>10;执行时间:0.078