MySQL中,Group By后,取每组的Top N条记录

转载自:http://www.geeklab.info/2012/11/mysql-how-to-get-top-n-rows-for-each-group/

We have the following table and data:

 

 

Session Variables

MySQL, at least on the versions I’ve checked, does not support ROW_NUMBER() function that can assign a sequence number within a group, the MySQL session variables can be used to build a workaround. Session variables do not need to be declared first and can be used to do calculations and perform actions based on them. They appear to require initialization. For instance:

@previous_haircolor := haircolor

This code is executed for each row and stores the value of haircolor column to @current_haircolor variable.

@girl_rank := IF(@previous_haircolor = haircolor, @girl_rank + 1, 1)

In this code, if @previous_haircolor equals the haircolor on the current row we increment rank, otherwise set it to 1. For the first row @previous_haircolor is NULL, so rank is also initialized to 1.

For correct ranking, we need to have ORDER BY haircolor,score DESC

So if we run:

When we have a rank assigned to each girl within her haircolor group, we can request the wanted range:

So the full query is:

Using oracle, SQL Server and PostgreSQL: ROW_NUMBER()

 

In Oracle, SQL Server and PostgreSQL (version 8.4 and higher) you can achieve the same functionality using ROW_NUMBER function:

Leave a Reply

Your email address will not be published.