隨著數據庫的發展，如今的數據庫可以儲存大量的數據，內存也是越來越大，但是無論您的內存多大，內存總是顯得不夠用，這時就要涉及到分頁，下文中將為大家帶來詳細的數據庫分頁操作。
1.Oracle:
| 12 | select * from ( select row_.*, rownum rownum_ from ( query_SQL ) row_ where rownum =< max) where rownum_ >= min |
2.SQL Server:
| 12 | select top @pagesize * from tablename where id not in(select top @pagesize*(@page-1) id from tablename order by id) order by id |
3.MySQL
| 1 | select * from tablename limit position, counter |
4.DB2
| 12 | select * from (select *,rownumber() as ROW_NEXT from tablename) where ROW_NEXT between min and max |
語句形式：
| 123456 | SELECT TOP 10 * FROM TestTable WHERE(ID NOT IN (SELECT TOP 20 id FROM TestTable ORDERBY id)) ORDERBYID SELECT TOP 頁大小 * FROM TestTable WHERE( ID NOT IN (SELECT TOP 每頁大小-1*待查詢頁數-1 id FROM 表 ORDERBY id)) ORDERBYID |
思路：先查詢出待查詢頁之前的全部條數的id，查詢ID不在這些ID中的指定數量條數。
分頁方案二：(利用ID大于多少和SELECT TOP分頁)效率最高語句形式：
| 123456 | SELECT TOP 10 * FROM TestTable WHERE(ID>(SELECT MAX(id) FROM(SELECT TOP20 id FROM TestTable ORDERBYid)AS T))ORDERBY ID SELECT TOP 頁大小* FROM TestTable WHERE(ID>(SELECT MAX(id) FROM(SELECT TOP 每頁大小*待查詢頁數-1 id FROM 表 ORDERBY id)AS T)) ORDERBY ID |
思路：先獲得待查詢頁的之前全部條數id，獲得它們當中最大的ID號，以此最大ID號為標志，查找比這個ID號大的指定條數。
分頁方案三：| 1234 | SELECT TOP PageSize * FROM(SELECT TOP nPage*PageSize * from YOURTABLE order by id) as a order by id descSELECT TOP 每頁條數 * FROM(SELECT TOP 待查詢頁*每頁條數) * from YOURTABLE order by id)as a order by id desc |
思路：先正排序查詢出待查詢頁之前(包括當前頁)的全部條數，然后將其倒排序，取指定條數。
新聞熱點
疑難解答
