Common Database Paging

1. Oracle Paging

The first: Using the Analytic function Row_number () method

SELECT * FROM (
Select T.*,row_number () over (order by t1.id) Rowno from TABLE1
)
where rowno between and 40;

Second: Direct use of rownum virtual column ( recommended )

SELECT * FROM
(select T.*,rownum as Rowno from TABLE1)
where Rowno between and 20

Third: Do not use between and for paging

SELECT * FROM

(SELECT a.*, RowNum R

From

(SELECT * from Msr_topic

Where name like%hr%

) A

WHERE rownum <= Indexend

) B

WHERE r > indexstart;

Summary: Obviously the second method is better, the third method is the worst! Because the order BY statement is not used, it can improve the speed of retrieving data, especially when the data volume is larger, the second method is more obvious to retrieve data quickly. the paging with order by is used with caution in Oracle. Especially in oracle10g, there will be confusion, that is, the same record will appear on different pages.

2. SQL Server Paging

First method: implemented by the top function ( recommended )

SELECT TOP Ten * from TestTable
WHERE (
ID not in (SELECT TOP $ * from testtable ORDER by ID)
)
ORDER by ID

The second approach: using the Max function to implement

Select Top Limit *
From table1
where id>
(select Max (ID) from
(select Top ((page-1) *limit) ID from the Table1 ORDER by ID) as T
)
ORDER BY ID

The third method: through the Row_number function +top to achieve

From
(
SELECT Top Limit Row_number () over (ORDER by ReportID) as RowNo
From TABLE
) as A
WHERE RowNo > page*10

Fourth method: by Row_number function +between and to implement

SELECT * FROM (SELECT * Row_number () + (order by u_id) as row_id from the users where u_name like%hr%) As Temp_userswhere row_id between ${start} and ${end}

Summarize:

1) The first method has a fatal disadvantage, that is, it contains not the word, to replace with not exists instead of in;

2) The second method note that the ID must be unique, cannot use a composite primary key, and the primary key must also be a regular number, otherwise it cannot be compared

3) The third method, Row_number (), is to generate a sequential line number, and the standard for his generation order is the next-to-last-order by ReportID, where ReportID can be a federated primary key. Needless to say, the worst way to bring your own function is not to use it.

4) as in the third way, inefficient

3. mysql Paging

SELECT * FROM table limit start, size

4. PostgreSQL Paging

SELECT * FROM table limit size offset start

Reference:

Many blogs ...

Common Database Paging