oracle select * from (select *,rownum from table where ...) where rownum between m and n;
sqlserver select top (n-m) * from(select top n * from table where ...) order by .. desc
oracle 抽取从N行到M行的记录,如从20行到30行的记录 select * from (select rownum id,t.* from table where …… and rownum <= 30) where id > 20;
liu_you的方法基本上是错误的. OWERT520的方法正确.
oracle语句中,如果记录条数特别多,用子查询的方法效果好吗?比如 select * from (select rownum id,t.* from table )如果十万条记录,也就是相当于要生成一个一万条记录的零时表, 会不会太慢?
select n-m from talbe where id not in (select m from table )
select n-m from talbe where id not in (select m from table ) 此方法需要排序,否则查出的结果很乱
1. ORACLE SELECT * FROM TABLE1 WHERE ROWNUM<=N 2. INFORMIX SELECT FIRST N * FROM TABLE1 where 1=1 3. DB2 SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N 或者 SELECT COLUMN FROM TABLE where 1=1 FETCH FIRST N ROWS ONLY 4. SQL SERVER SELECT TOP N * FROM TABLE1 where 1=1 or SET ROWCOUNT N SELECT * FROM TABLE1 where 1=1 SET ROWCOUNT N15. SYBASE SET ROWCOUNT N SELECT * FROM TABLE1 where 1=1 SET ROWCOUNT N16. MYSQL SELECT * FROM TABLE1 where 1=1 LIMIT N 7. FOXPRO SELECT * TOP N FROM TABLE ORDER BY COLUMN8. ACCESS SELECT TOP N * FROM TABLE1 where 1=1
抽取从N行到M行的记录,如从20行到30行的记录
select * from (select rownum id,t.* from table where ……
and rownum <= 30) where id > 20;
OWERT520的方法正确.
会不会太慢?
此方法需要排序,否则查出的结果很乱
SELECT * FROM TABLE1 WHERE ROWNUM<=N 2. INFORMIX
SELECT FIRST N * FROM TABLE1 where 1=1 3. DB2
SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM<=N
或者
SELECT COLUMN FROM TABLE where 1=1 FETCH FIRST N ROWS ONLY 4. SQL SERVER
SELECT TOP N * FROM TABLE1 where 1=1
or
SET ROWCOUNT N SELECT * FROM TABLE1 where 1=1 SET ROWCOUNT N15. SYBASE
SET ROWCOUNT N SELECT * FROM TABLE1 where 1=1 SET ROWCOUNT N16. MYSQL
SELECT * FROM TABLE1 where 1=1 LIMIT N 7. FOXPRO
SELECT * TOP N FROM TABLE ORDER BY COLUMN8. ACCESS
SELECT TOP N * FROM TABLE1 where 1=1
具体语句可以参考hibernate,全部是最优化的语句。
select * from (select rownum id,t.* from table where ……
and rownum <= 30) where id > 20;
如果是oracle的话,必须要三重循环才行的,楼上五星的二重循环是没用的。
具体语句可以参考hibernate,全部是最优化的语句。
---------------------------------------
得到了一些提示,hibernate里边可以设置分页,
你使用不同的数据库,然后看输出的sql语句,就可以了,
不过就是太 麻烦!!!