What are the methods of extracting the first N% records in the database?

This article mainly explains "what are the methods of extracting the top N% records in the database". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. let's go deep into the editor's train of thought. Let's study and learn "what are the methods of extracting the first N% records in the database"?

This requirement is easy to implement on SQL Server and Oracle, even on MySQL 8.0, but our business database happens to be MySQL 5.7.

First of all, I will introduce the implementation methods of different database platforms.

SQL Server implementation method

There is a TOP Percent method on SQL Server that can directly take the first (or last) percent of the result, for example, there is the following City table

The first 10% of our data records can be written as follows:

SELECT TOP 10 PERCENT * FROM City ORDER BY ID DESC

The results are as follows:

Oracle implementation method

Oracle has a ROWNUM pseudo column that can be used to help us calculate the top N percent. Characteristics of ROWNUM pseudo columns:

The ROWNUM is sorted in the order in which the records were inserted

ROWNUM does not actually exist. It is a sort of filtered result set. If there is no result set, there will be no ROWNUM.

ROWNUM cannot be prefixed with a base table name

When using ROWNUM for queries, note that:

1) if you use the greater than sign (>), you can only use (> 0), and nothing else can be used 2) if you use the less than sign (