Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to solve the mysql top n problem". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In daily work, it is often necessary to query the top of the grouping or the first few records of the query data (articles 5 to 10), and so on.

TOP-N analysis is to get the required N data from the research object through the TOP-N algorithm, and select the largest or smallest N data from the sorted list, which is a TOP-N algorithm.

Rownum is used in limit;oracle in mysql.

There is no top in mysql

If you want to check the first few pieces of data, you should use the sorter to check it.

Order by id desc limit 010 takes out the first 10 items in reverse order of id.

Order by id limit 5 and 10 are sorted according to the positive order of id, starting with Article 5 and taking 10 items.

Migrating a program from mysql to oracle encountered a limit problem in the sql statement.

Check all over the network, the methods provided are extremely troublesome, not conducive to general purpose.

Here is my solution, which is comparable to limit.

For example, query 2 to 6 records from a mobileuser user table, sorted by the time of first use.

The mysql statement is:

SELECT userid,password,firstusetime from mobileuser ORDER BY firstusetime DESC limit 2,6

The oracle statement is:

SELECT * FROM (SELECT userid,password,firstusetime, RANK () OVER (ORDER BY firstusetime DESC) RN FROM mobileuser) WHERE RN between 2 and 6

The rank () over function represents the ranking basis, and the entire sql statement finds data that ranks 2 to 6 bits according to the "ORDER BY firstusetime DESC" standard.

But there is a problem, according to the ranking basis, some data is juxtaposed, so that the number of data returned will be more than we expect. At this point, we can just add a rownum limit.

SELECT * FROM (SELECT userid,password,firstusetime, RANK () OVER (ORDER BY firstusetime DESC) RN FROM mobileuser) WHERE RN between 2 and 6 and rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.