Obtenir des n premiers enregistrements pour chaque groupe de résultats groupés

Dans d'autres bases de données que vous pouvez faire cela en utilisant ROW_NUMBER. MySQL ne supporte pas ROW_NUMBER mais vous pouvez utiliser des variables pour l'émuler:

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

Voir en ligne: sqlfiddle

Modifier je viens de remarquer que bluefeet posté un très semblables réponse: +1 pour lui. Cependant cette réponse a deux petits avantages:

1. Il est une seule requête. Les variables sont initialisées à l'intérieur de l'instruction SELECT.
2. Il gère les liens comme décrit dans la question (en ordre alphabétique par nom).

Donc je vais le laisser ici au cas où ça pourrait aider quelqu'un.

OriginalL'auteur Mark Byers

Comment sur l'utilisation de l'auto-assemblage:

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;

me donne:

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

J'ai été fortement inspiré par la réponse de Bill Karwin à Select top 10 des dossiers pour chaque catégorie

Aussi, je suis en utilisant SQLite, mais cela doit fonctionner sur MySQL.

Une autre chose: dans le ci-dessus, j'ai remplacé le group colonne avec un groupname colonne pour plus de commodité.

Modifier:

Suivi des OP de commentaire concernant l'absence de cravate résultats, j'ai incrémenté sur snuffin de réponse pour afficher tous les liens. Cela signifie que si les derniers sont des liens, plus de 2 lignes peuvent être retournés, comme indiqué ci-dessous:

.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;

me donne:

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

OriginalL'auteur

Snuffin solution semble assez lent à exécuter lorsque vous avez beaucoup de lignes et de Marque Byers/Rick James et Bluefeet solutions ne fonctionne pas sur mon environnement (MySQL 5.6), car l'ordre est appliqué après l'exécution de sélectionner, voici donc une variante de Marc Byers/Rick James solutions pour résoudre ce problème (avec un supplément de imbriquées à choisir):

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;

J'ai essayé de requête similaire sur une table de 5 millions de lignes et il renvoie le résultat en moins de 3 secondes

OriginalL'auteur Laurent PELE

Je voulais partager cela car j'ai passé un long moment à la recherche d'un moyen facile de l'appliquer dans un programme java, je travaille sur. Ce n'est pas tout à fait donner la sortie que vous recherchez, mais ses proches. La fonction mysql appelée GROUP_CONCAT() a vraiment bien travaillé pour spécifier le nombre de résultats à retourner dans chaque groupe. À l'aide de LIMIT ou de toute autre fantaisie de façons d'essayer de le faire avec COUNT ne fonctionne pas pour moi. Donc, si vous êtes prêt à accepter une modification de la sortie, c'est une excellente solution. Disons que j'ai une table appelée "étudiant" avec id d'étudiants, de leur sexe, et de la gpa. Disons que je veux le top 5 gpas pour chacun des sexes. Ensuite, je peux écrire la requête de ce

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

Notez que le paramètre "5", dit-il combien d'entrées pour concaténer dans chaque ligne

Et la sortie devrait ressembler à quelque chose comme

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

Vous pouvez également modifier la ORDER BY variable et de leur ordonner d'une manière différente. Donc, si j'avais l'âge de l'étudiant je pouvais remplacer la "gpa desc' avec 'âge desc' et ça marche! Vous pouvez également ajouter des variables à l'instruction group by pour obtenir plus de colonnes dans le résultat. Donc, c'est juste un moyen que j'ai trouvé qui est assez flexible et fonctionne bien si vous êtes ok avec juste la liste des résultats.

OriginalL'auteur Jon Bown

Il y a vraiment une belle réponse à ce problème à MySQL - Comment Obtenir les N premières Lignes par Chaque Groupe

Basé sur la solution dans le lien référencé, votre requête serait comme:

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;

où n est le top n et your_table est le nom de votre table.

Je pense que l'explication de la référence est vraiment clair. Pour une référence rapide, je vais le copier et le coller ici:

Actuellement MySQL ne prend pas en charge la fonction ROW_NUMBER() qui peut affecter
un numéro de séquence au sein d'un groupe, mais comme une solution de contournement, nous pouvons utiliser MySQL
les variables de session.

Ces variables ne nécessitent pas de déclaration, et peut être utilisé dans une requête
pour effectuer des calculs et pour stocker les résultats intermédiaires.

@current_country := pays de Ce code est exécuté pour chaque ligne et
stocke la valeur de la colonne à @current_country variable.

@country_rank := SI(@current_country = pays, @country_rank + 1, 1)
Dans ce code, si @current_country est la même que nous incrément de rang,
sinon mis à 1. Pour la première ligne @current_country est NULLE, de sorte
rang est également mis à 1.

Pour corriger classement, nous avons besoin d'ORDRE PAR pays, de la population DESC

OriginalL'auteur swdon