I view the post of predecessors, all due to the database of SQL large inconsistency, and SQL Server,access and other rookie-level database, such as rowid,_n_,obs and other auxiliary columns, empty between operators and no useful,
and Wuru the data set operators like except, which makes countless heroes.
I have a detailed view of each database SQL, that is, the database has to take the first n records of the SQL syntax, such as what select top n***** syntax, and data paging key problem is to take after the N records of the grammar I thought long, finally small enlightenment, so out of this, but also forget the predecessors of many guidance
The Dafa that takes the record set after N records:
Assume:
1. One SQL statement will produce 1000 records (select Unique ID, other field from table set where condition order by sort)
2. Display 20 records per page
3. Current Display 5th page
Implemented as follows:
SELECT * FROM
(
SELECT * FROM (select top 20*4 Unique ID, other fields from table set where condition order by sort) as a
UNION ALL
SELECT * FROM (select top 20*5 Unique ID, other fields from table set where condition order by sort) as B
)
A
GROUP by Unique ID, other fields have count (unique ID) =1 ORDER BY
Run this SQL, this is the end of the recordset N records Dafa is over.
Detailed Description:
The key application technique for this SQL statement is union ALL and conditional filter statements on groups
You can use this technique to make a common paging method, such as directly by the user incoming SQLSTR (SQL statements), Numperpage (per page display number), Currpage (current page), oneself in the function of the organized into a SQL paging statement
Note:
If the current page is 1, then you don't need to run the SQL, just a top is OK
Because there is no environment, this SQL performance cannot be tested, but I believe it will not be inefficient
Running platform on the access,sqlserver can be run, other database platform only need to change the top keyword should be able to run in place