Paging algorithms for each database, Oracle intersect and minus operations

1. SQL Server 2000 is a method that uses reverse and forward order first.

Select top 3 * from (select Top 9 * From mvc_book order by bid) as s order by S. Bid DESC [Article 6, 5, 4]

Select * from (select top 3 * from (select top 6 * From mvc_book order by bid) as s order by S. bid DESC) as S1 order by s1.bid [Article 4, 5, 6]

Select top 6 * From mvc_book where (BID not in (select Top 2 bid from mvc_book) [entries 3, 4, 5, 6, 7, 8]

Sql2005

With, row_number (number of rows) and over

This is very fresh and useful for SQL Server 2005. The following example shows how to get 20 to 19 records from a result set. I was a little surprised at the beginning, but I found it was so simple after browsing the queryer.

  With Cust
(Select customerid, companyName,
Row_number () over (order by companyName) as rownumber
From MERs mers)
Select *
From cust
Where rownumber between 20 and 30
 

In SQL Server 2005, with specifies a temporary name, similar to the temporary table in SQL Server versions earlier. However, the input part is the row_number and over statement, which creates the numbers in each group according to the company name. This is like adding an identity seed to the temporary table through command provision.
I hope you agree with me. If not, runCodeAnd view the result set. For large tables, the speed is really very fast. I was deeply impressed by the fact that the speed of tables exceeds 250,000 records.

 

2. Oracle uses minus and rownum (minus Union union Union intersect returns the same record set)

Select * From t_service_vnet_send where rownum <= 15 minus select * From t_service_vnet_send where rownum <= 10;
Or
Select * from (select rownum No, ID, age, name from loaddata where rownum <= 3) where no> = 2;

3. DB2
Select * from (select row_number () over () as a, org. * From org) as temp where a> = N1 and a <= n2

4. MySQL
Select * From tablename limit M, N;

 

 

 

Two useful Oracle Database Operations: intersect and minus

 

Intersect operations
Returns the same part of the query results.
Exp: Which types of jobs are the same in each department?
Select job
From account
Intersect
Select job
From Research
Intersect
Select job
From sales;

minus operation
Returns records of rows that are different from the second query result in the first query.
which types of work are included in the finance department but not in the sales department?
Exp: Select job from account
minus
select job from sales;