Uzyskaj najwyższe N rekordów dla każdej grupy wyników zgrupowanych

W innych bazach danych można to zrobić za pomocą ROW_NUMBER. MySQL nie obsługuje ROW_NUMBER , ale możesz użyć zmiennych, aby go emulować:

SELECT
    person,
    groupname,
    age
FROM
(
    SELECT
        person,
        groupname,
        age,
        @rn := IF(@prev = groupname, @rn + 1, 1) AS rn,
        @prev := groupname
    FROM mytable
    JOIN (SELECT @prev := NULL, @rn := 0) AS vars
    ORDER BY groupname, age DESC, person
) AS T1
WHERE rn <= 2

Zobacz to działa online: sqlfiddle

Edit właśnie zauważyłem, że bluefeet napisał do niego bardzo podobną odpowiedź: +1. Jednak ta odpowiedź ma dwie małe zalety:

1. jest to pojedyncze zapytanie. Zmienne są inicjowane wewnątrz instrukcji SELECT.
2. obsługuje krawaty opisane w pytaniu (kolejność alfabetyczna według nazwy).

Spróbuj tego:

SELECT a.person, a.group, a.age FROM person AS a WHERE 
(SELECT COUNT(*) FROM person AS b 
WHERE b.group = a.group AND b.age >= a.age) <= 2 
ORDER BY a.group ASC, a.age DESC

DEMO

Jak o użyciu samo-łączenia:

CREATE TABLE mytable (person, groupname, age);
INSERT INTO mytable VALUES('Bob',1,32);
INSERT INTO mytable VALUES('Jill',1,34);
INSERT INTO mytable VALUES('Shawn',1,42);
INSERT INTO mytable VALUES('Jake',2,29);
INSERT INTO mytable VALUES('Paul',2,36);
INSERT INTO mytable VALUES('Laura',2,39);

SELECT a.* FROM mytable AS a
  LEFT JOIN mytable AS a2 
    ON a.groupname = a2.groupname AND a.age <= a2.age
GROUP BY a.person
HAVING COUNT(*) <= 2
ORDER BY a.groupname, a.age DESC;

Daje mi:

a.person    a.groupname  a.age     
----------  -----------  ----------
Shawn       1            42        
Jill        1            34        
Laura       2            39        
Paul        2            36      

Zainspirowała mnie odpowiedź Billa Karwina, aby wybrać top 10 płyt dla każdej kategorii

Również używam SQLite, ale to powinno działać na MySQL.

Inna sprawa: w powyższym przykładzie dla wygody zamieniłem kolumnę group na kolumnę groupname.

Edit :

W związku z komentarzem OP dotyczącym brakujących wyników remisu, dodałem tabakierkę Odpowiedz, aby pokazać wszystkie krawaty. Oznacza to, że jeśli ostatnie są wiązaniami, można zwrócić więcej niż 2 wiersze, jak pokazano poniżej:

.headers on
.mode column

CREATE TABLE foo (person, groupname, age);
INSERT INTO foo VALUES('Paul',2,36);
INSERT INTO foo VALUES('Laura',2,39);
INSERT INTO foo VALUES('Joe',2,36);
INSERT INTO foo VALUES('Bob',1,32);
INSERT INTO foo VALUES('Jill',1,34);
INSERT INTO foo VALUES('Shawn',1,42);
INSERT INTO foo VALUES('Jake',2,29);
INSERT INTO foo VALUES('James',2,15);
INSERT INTO foo VALUES('Fred',1,12);
INSERT INTO foo VALUES('Chuck',3,112);


SELECT a.person, a.groupname, a.age 
FROM foo AS a 
WHERE a.age >= (SELECT MIN(b.age)
                FROM foo AS b 
                WHERE (SELECT COUNT(*)
                       FROM foo AS c
                       WHERE c.groupname = b.groupname AND c.age >= b.age) <= 2
                GROUP BY b.groupname)
ORDER BY a.groupname ASC, a.age DESC;

Daje mi:

person      groupname   age       
----------  ----------  ----------
Shawn       1           42        
Jill        1           34        
Laura       2           39        
Paul        2           36        
Joe         2           36        
Chuck       3           112      

Zobacz to:

SELECT
  p.Person,
  p.`Group`,
  p.Age
FROM
  people p
  INNER JOIN
  (
    SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`
    UNION
    SELECT MAX(p3.Age) AS Age, p3.`Group` FROM people p3 INNER JOIN (SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`) p4 ON p3.Age < p4.Age AND p3.`Group` = p4.`Group` GROUP BY `Group`
  ) p2 ON p.Age = p2.Age AND p.`Group` = p2.`Group`
ORDER BY
  `Group`,
  Age DESC,
  Person;

SQL: http://sqlfiddle.com/#! 2 / cdbb6/15

Rozwiązanie Snuffin wydaje się dość powolne w wykonaniu, gdy masz dużo wierszy i rozwiązania Mark Byers / Rick James i Bluefeet nie działają na moim środowisku (MySQL 5.6), ponieważ order by jest stosowane po wykonaniu select, więc oto wariant rozwiązań Marc Byers/Rick James, aby rozwiązać ten problem (z dodatkowym imbricated select):

select person, groupname, age
from
(
    select person, groupname, age,
    (@rn:=if(@prev = groupname, @rn +1, 1)) as rownumb,
    @prev:= groupname 
    from 
    (
        select person, groupname, age
        from persons 
        order by groupname ,  age desc, person
    )   as sortedlist
    JOIN (select @prev:=NULL, @rn :=0) as vars
) as groupedlist 
where rownumb<=2
order by groupname ,  age desc, person;

Wypróbowałem podobne zapytanie na tabeli zawierającej 5 milionów wierszy i zwraca wynik w czasie krótszym niż 3 sekundy

Jeśli Pozostałe odpowiedzi nie są wystarczająco szybkie daj ten kod spróbuj:

SELECT
        province, n, city, population
    FROM
      ( SELECT  @prev := '', @n := 0 ) init
    JOIN
      ( SELECT  @n := if(province != @prev, 1, @n + 1) AS n,
                @prev := province,
                province, city, population
            FROM  Canada
            ORDER BY
                province   ASC,
                population DESC
      ) x
    WHERE  n <= 3
    ORDER BY  province, n;

Wyjście:

+---------------------------+------+------------------+------------+
| province                  | n    | city             | population |
+---------------------------+------+------------------+------------+
| Alberta                   |    1 | Calgary          |     968475 |
| Alberta                   |    2 | Edmonton         |     822319 |
| Alberta                   |    3 | Red Deer         |      73595 |
| British Columbia          |    1 | Vancouver        |    1837970 |
| British Columbia          |    2 | Victoria         |     289625 |
| British Columbia          |    3 | Abbotsford       |     151685 |
| Manitoba                  |    1 | ...

Chciałem się tym podzielić, ponieważ długo szukałem łatwego sposobu na zaimplementowanie tego w programie java, nad którym pracuję. To nie daje wyjścia, którego szukasz, ale jest blisko. Funkcja w mysql o nazwie GROUP_CONCAT() działała naprawdę dobrze do określenia, ile wyników ma być zwróconych w każdej grupie. Używanie LIMIT lub innych wymyślnych sposobów na zrobienie tego z COUNT nie zadziałało na mnie. Więc jeśli chcesz zaakceptować zmodyfikowane wyjście, jest to świetne rozwiązanie. Powiedzmy, że mam tabela o nazwie "student" z identyfikatorami uczniów, ich płcią i średnią ocen. Powiedzmy, że chcę top 5 GPA dla każdej płci. Następnie mogę napisać zapytanie w ten sposób

SELECT sex, SUBSTRING_INDEX(GROUP_CONCAT(cast(gpa AS char ) ORDER BY gpa desc), ',',5) 
AS subcategories FROM student GROUP BY sex;

Zauważ, że parametr "5" mówi, ile wpisów należy połączyć w każdy wiersz

I wyjście wyglądałoby jak

+--------+----------------+
| Male   | 4,4,4,4,3.9    |
| Female | 4,4,3.9,3.9,3.8|
+--------+----------------+

Można również zmienić zmienną ORDER BY I zamówić je w inny sposób. Więc gdybym miał wiek ucznia, mógłbym zastąpić 'gpa desc' na 'age desc' i będzie działać! Możesz Dodaj również zmienne do instrukcji group by, aby uzyskać więcej kolumn w wyjściu. Tak więc jest to po prostu sposób, który znalazłem, że jest dość elastyczny i działa dobrze, jeśli nie masz nic przeciwko tylko wynikom aukcji.

W Sql Server row_numer() jest potężną funkcją, która może łatwo uzyskać wynik jak poniżej

select Person,[group],age
from
(
select * ,row_number() over(partition by [group] order by age desc) rn
from mytable
) t
where rn <= 2

Jest naprawdę fajna odpowiedź na ten problem w MySQL-jak uzyskać górne n wierszy dla każdej grupy

W oparciu o rozwiązanie w odnośnym linku, Twoje zapytanie będzie wyglądać następująco:

SELECT Person, Group, Age
   FROM
     (SELECT Person, Group, Age, 
                  @group_rank := IF(@group = Group, @group_rank + 1, 1) AS group_rank,
                  @current_group := Group 
       FROM `your_table`
       ORDER BY Group, Age DESC
     ) ranked
   WHERE group_rank <= `n`
   ORDER BY Group, Age DESC;

Gdzie n to top n i your_table to nazwa Twojej tabeli.

Myślę, że wyjaśnienie w odnośniku jest naprawdę jasne. Dla szybkiego odniesienia skopiuję i wkleję go tutaj:

Obecnie MySQL nie obsługuje funkcji ROW_NUMBER (), która może przypisać Sekwencja numer w grupie, ale jako obejście możemy użyć MySQL zmienne sesji.

Te zmienne nie wymagają deklaracji i mogą być użyte w zapytaniu do wykonywania obliczeń i przechowywania wyników pośrednich.

@ current_country: = country ten kod jest wykonywany dla każdego wiersza i przechowuje wartość kolumny country do zmiennej @ current_country.

@ country_rank: = IF(@current_country = country, @country_rank + 1, 1) W tym kodzie, jeśli @current_country jest tym samym zwiększamy rangę, w przeciwnym razie Ustaw na 1. Dla pierwszego wiersza @current_country jest NULL, więc ranga jest również ustawiona na 1.

Aby uzyskać poprawny ranking, musimy uporządkować według kraju, populacji DESC