第n页. 每页显示x条 mysql: SELECT * FROM USER limit (n - 1) * x, xsql2000: SELECT * FROM (SELECT TOP n * x FROM USER) WHERE id NOT IN (SELECT TOP (n - 1) * x id FROM USER);oracle: SELECT * FROM ( SELECT users.*, ROWNUM RN FROM (SELECT * FROM USER) users WHERE ROWNUM < (n * x) ) WHERE RN >= (n - 1) * x这些语句是优化过的,比别的查询速度要快很多,不信你可以对照别的语句去测试,希望对楼主有所帮助
传递参数,拼SQL row是行数 pageNum是页码-1select top(row) * from TABLE where id not in(select top(row*pageNum) id from TABLE) 比如第1页 ,每页10条记录SQL就变成了 select top(10) * from TABLE where id not in(select top(0) id from TABLE) 结果是1-10的记录比如第2页 ,每页10条记录SQL就变成了 select top(10) * from TABLE where id not in(select top(1*10) id from TABLE) 结果是11到20的记录
select * from student limit 0,10oracle:
select * from (select *,rownum as r from student where rownum < 10) where r > 0;
第n页. 每页显示x条
mysql: SELECT * FROM USER limit (n - 1) * x, xsql2000: SELECT * FROM (SELECT TOP n * x FROM USER) WHERE id NOT IN (SELECT TOP (n - 1) * x id FROM USER);oracle: SELECT * FROM (
SELECT users.*, ROWNUM RN FROM (SELECT * FROM USER) users
WHERE ROWNUM < (n * x)
)
WHERE RN >= (n - 1) * x这些语句是优化过的,比别的查询速度要快很多,不信你可以对照别的语句去测试,希望对楼主有所帮助
row是行数
pageNum是页码-1select top(row) * from TABLE where id not in(select top(row*pageNum) id from TABLE)
比如第1页 ,每页10条记录SQL就变成了
select top(10) * from TABLE where id not in(select top(0) id from TABLE)
结果是1-10的记录比如第2页 ,每页10条记录SQL就变成了
select top(10) * from TABLE where id not in(select top(1*10) id from TABLE)
结果是11到20的记录
+ user_id;
if (!myself) {
sql += " AND publish = 1";
}
// String type_id = request.getParameter("type_id");
if (null != type_id && type_id.length() > 0) {
// System.out.println(type_id.length());
sql += " AND type_id=" + type_id;
}
sql += " LIMIT " + (currect_page - 1) * total_prepage + ","
+ total_prepage;
在SQL查询时分批次查询或者直接查出所有,然后批次显示