select * from (select rownum rid,* from table) where rid between 10 and 20;
david_xu322(咖啡原來是苦的) 你到底懂不懂Oracle不懂就别往上写。猪。
我们可以根据实际情况来选择适合自己的方法,我给大家介绍一个通过标准的SQL语句来得到符合条件的数据。如从第10到20条的符合条件(where语句中的)的记录。通过这种方法取得记录有一个必要条件,必须有一个能够标识记录顺序的字段,如id,time等等。下面我为大家演示一个例子: 查询t_table表中所有记录第10到20条,按id排序。 SQL语句为: SELECT * FROM t_table t1 WHERE (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id ) >= 10 AND (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id ) < 20又如查询t_table表中key='123'第10到20条的记录,按id排序。 SELECT * FROM t_table t1 WHERE (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id AND t2.key = '123') >= 10 AND (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id AND t2.key = '123') < 20 AND t1.key = '123'
select * from table where rowid between 10 and 20;
select * from (select rownum tid,t.* from unit_define t) where tid between 10 and 20;
select * from table_name where rownum<=20 minus select * from table_name where rownum<10
我刚测试了一下,下面的写法是正确的 select * from table_name where rownum <= 20 minus select * from table_name where rownum < 10;
查询t_table表中所有记录第10到20条,按id排序。
SQL语句为:
SELECT * FROM t_table t1 WHERE (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id ) >= 10 AND (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id ) < 20又如查询t_table表中key='123'第10到20条的记录,按id排序。
SELECT * FROM t_table t1 WHERE (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id AND t2.key = '123') >= 10 AND (SELECT count(*) FROM t_table t2 WHERE t2.id < t1.id AND t2.key = '123') < 20 AND t1.key = '123'
select * from table_name where rownum<=20
minus
select * from table_name where rownum<10
select * from table_name where rownum <= 20
minus
select * from table_name where rownum < 10;