SQL/Oracle Method for retrieving records from MB to n

The SQL/Oracle Method for retrieving records from the MTH to the n th to use a single SQL statement to retrieve records from the Table from the MTH to the n (Not In version) select top n-m + 1 * FROM Table WHERE (id not in (select top S-1 id FROM Table) -- extracts records FROM the TABLE Table (Exists Version) select top n-m + 1 * from table as a WHERE Not Exists (Select * From (Select Top m-1 * From TABLE order by id) B Where B. id =. id) Order by id -- m is the superscript, and n is the subscript. For example, extract 8th to 12 records, m = 8, n = 12, table is the Table name Select Top n-m + 1 * From Table Where Id> (Select Max (Id) From (Select Top M-1 Id From Table Order By Id Asc) Temp) order By Id Asc directly retrieves the paging records in the database, provided that the table must have a primary key to retrieve the N records after the M records: select top n * FROM [TABLE] WHERE (id not in (select top m id FROM [TABLE] order by [ORDER]) Implementation in order by [ORDER] Oracle, obtain records from m to N: SELECT * FROM (SELECT *, rownum as con from (SELECT * FROM [TABLE] order by [ORDER]) where rownum <= N) where con> = M; Query several consecutive records in A table. Do not pass the condition parameters of any column. query several consecutive records in the table, such as table, id column primary key id name sex age ----------------------- 1 luoyi male 21 2 yaya female 20 3 lili female 22 4 wuyong male 25 ................ ....... there are still a lot of records in this table. If I want to take the second and third records, I just want these two records, which will be used not only in programming, in addition, some companies also have similar questions during interviews (Haha, I have never met). in oracle and mssqlserver, the SQL code is: 1. Oracle cannot use the top keyword in oracle, rownum can be used in two ways. (select * from A where rownum <= 4) minus (select * from A whe Re rownum <= 1). In this way, two or three records are obtained. The minus keyword is used to calculate the difference set of two result sets. in mathematics, for example, two sets can be merged, public, and difference set. 2. select * from (select * from A where rownum <4) B where B. the Code of id not in (select id from A where rownum <2) can also be implemented. The not in operator is mainly used. 2. ms SQL server does not have minus in the server. Only the second method similar to oracle can be used to select * from (select top 3 * from) as B where B. id not in (select top 1 id from A) 3. The result is: id name sex age ------------------------------------ 2 yaya female 20 3 lili female 22. query N records in the database, then, if you see this topic in sorting the N records, some people will write this sentence, select top 10 * from tablename order by createtime this statement is exactly the opposite of the topic. Correct answer: 1: select top 10 * from TableName where Id in (select top 10 id from TableName order by id) order by createtime statement, you can find the first 10 records in the table, then, in the order of the createtime time, the table requires a primary key answer 2. If there is no primary key, you can also SELECT * FROM (select top 10 * FROM titles) mm order by pubdate desc SQL statement for randomly retrieving several records SQL server: select top 20 * from Table order by newid () Access: SELECT top 20 * FROM Table order by Rnd (id) where the id is an automatic number field, you can use any other value to complete, for example, using the name field (UserName) SELECT top 20 * FROM Table ORD Er by Rnd (len (UserName) MySql: Select * From Table Order By rand () Limit 20 features: one query, the database returns only one page of data. Instead of getting all the data.: Pagesize: number of records displayed per page cureentpage: select * from (select TOP pagesize * FROM (select top pagesize * cureentpage * from user_table order by id ASC) as aSysTable order by id DESC) as bSysTable order by id ASC example: assume that the database table is as follows: user_table: id: primary key, auto-increment username: character password: assume that there are 80 records and 10 records are displayed on each page. IDs are sorted in ascending order of IDs from 1 to 80. The data on the third page should be: the id of the obtained record should be 21 to 30. This statement should be: select * from (select TOP 10 * FROM (select top 30 * from user_table order by id ASC) as aSysTable order by id DESC) as b0000able order by id ASC: first, 30 records (3*10) are retrieved in ascending ORDER of IDS, that is: records with IDs between 1 and 30 (select top 30 * from user_table order by id ASC) are sorted in descending order by id: extract the first 10 records from 30 to 1: The obtained records are: IDs are between 30 and 21. This is the data we need, but it is arranged in descending order and does not meet the requirements. Finally, the data we need will be obtained after re-sorting. The id is between and 30.