对于有主键自动增长数据库表,在进行分页设置时,我认为还比较不错的是:
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
在oracle中不支持top所以我又找了一个,看下面的
string sql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM dx_friends where " + sqlwhere + " ) A WHERE ROWNUM <= " + pagesize * page + " )WHERE RN >= " + ((page - 1) * pagesize + 1);