转载自:http://www.geeklab.info/2012/11/mysql-how-to-get-top-n-rows-for-each-group/
We have the following table and data:
CREATE TEMPORARY TABLE girls(
name text,
haircolor text,
score INT
);
INSERT INTO girls VALUES ('Megan','brunette',9);
INSERT INTO girls VALUES ('Tiffany','brunette',5);
INSERT INTO girls VALUES ('Kimberly','brunette',7);
INSERT INTO girls VALUES ('Hester','blonde',10);
INSERT INTO girls VALUES ('Caroline','blonde',5);
SELECT * from girls;
+----------+-----------+-------+
| name | haircolor | score |
+----------+-----------+-------+
| Megan | brunette | 9 |
| Tiffany | brunette | 5 |
| Kimberly | brunette | 7 |
| Hester | blonde | 10 |
| Caroline | blonde | 5 |
+----------+-----------+-------+
5 rows in set (0.00 sec)
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:
SELECT @previous_haircolor := null; /* Initialize first, or girl_rank will always be 1 */
SELECT
name,haircolor,score,
@girl_rank := IF(@previous_haircolor = haircolor, @girl_rank + 1, 1) AS girl_rank,
@previous_haircolor := haircolor
FROM girls
ORDER BY haircolor,score DESC
We get the list of girls ranked by their score within the haircolor group:
+----------+-----------+-------+-----------+----------------------------------+
| name | haircolor | score | girl_rank | @previous_haircolor := haircolor |
+----------+-----------+-------+-----------+----------------------------------+
| Hester | blonde | 10 | 1 | blonde |
| Caroline | blonde | 5 | 2 | blonde |
| Megan | brunette | 9 | 1 | brunette |
| Kimberly | brunette | 7 | 2 | brunette |
| Tiffany | brunette | 5 | 3 | brunette |
+----------+-----------+-------+-----------+----------------------------------+
5 rows in set (0.00 sec)
When we have a rank assigned to each girl within her haircolor group, we can request the wanted range:
-- Get top 2 for each haircolor
SELECT name, haircolor, score
FROM (/*subquery above*/) ranked
WHERE girl_rank <= 2;
So the full query is:
SELECT @previous_haircolor := null; /* Initialize first, or girl_rank will always be 1 */
SELECT name, haircolor, score
FROM (SELECT
name,haircolor,score,
@girl_rank := IF(@previous_haircolor = haircolor, @girl_rank + 1, 1) AS girl_rank,
@previous_haircolor := haircolor
FROM girls
ORDER BY haircolor,score DESC) ranked
WHERE girl_rank <= 2;
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:
SELECT name,haircolor,score
FROM
(SELECT name,haircolor,score
ROW_NUMBER() OVER (PARTITION BY haircolor ORDER BY score DESC) as girl_rank
FROM girls) ranked
WHERE girl_rank <= 2;