MySQL "Group By" et "Order By"

Question

Je veux être en mesure de sélectionner un groupe de lignes d'une table des e-mails et de les regrouper par la part de l'expéditeur. Ma requête ressemble à ceci:

SÉLECTIONNEZ emailID, fromEmail, subject, timestamp, MIN(read) comme read DE incomingEmails OÙ toUserID = '$userID' GROUPE PAR la BAISSE(fromEmail) COMMANDE PAR timestamp DESC

La requête fonctionne presque comme je le veux--il sélectionne les enregistrements regroupés par e-mail tel qu'il n'existe qu'une seule ligne par e-mail. Le problème est que le emailID, l'objet et le timestamp ne correspond pas nécessairement à la plus récente des e-mails d'un particulier à partir de l'e-mail.

Par exemple, il peut retourner:

De: john@example.com Sujet: bonjour

De: mark@example.com Sujet: bienvenue

Lorsque les enregistrements dans la base de données sont:

De: john@example.com Sujet: bonjour

De: john@example.com Sujet: question de programmation

De: mark@example.com Sujet: bienvenue

Si la programmation de la question de l'e-mail est la plus récente, comment puis-je obtenir MySQL pour sélectionner l'enregistrement lorsque vous regroupez les e-mails?

Answer 1

Une solution simple consiste à envelopper la requête dans une sous-sélection avec le relevé de l'ORDRE de la première et de l'application de la GROUPE PAR plus tard:

SELECT * FROM ( 
    SELECT emailID, fromEmail, subject, timestamp, MIN(read) as read 
    FROM incomingEmails 
    WHERE toUserID = '$userID' 
    ORDER BY timestamp DESC
) AS tmp_table GROUP BY LOWER(fromEmail)

Ceci est similaire à l'aide de la rejoindre, mais ressemble beaucoup plus agréable.

À l'aide de non-agrégation des colonnes dans un SELECT avec une clause GROUP BY est non-standard. MySQL retournera les valeurs de la première ligne qu'il trouve et jeter le reste. Toute COMMANDE PAR clauses ne s'appliquent à la valeur de la colonne retournée, de ne pas écarté ceux.

Voir http://www.cafewebmaster.com/mysql-order-sort-group

Answer 2

Voici une approche:

 SELECT cur.textID, cur.fromEmail, cur.subject, 
     cur.timestamp, cur.read
FROM incomingEmails cur
LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.timestamp < next.timestamp
WHERE next.timestamp is null
and cur.toUserID = '$userID' 
ORDER BY LOWER(cur.fromEmail)
 

Fondamentalement, vous joignez la table sur lui-même, en recherchant les lignes suivantes. Dans la clause where, vous indiquez qu'il ne peut y avoir de lignes ultérieures. Cela ne vous donne que la dernière ligne.

S'il peut y avoir plusieurs courriels avec le même horodatage, cette requête devra être affinée. S'il existe une colonne d'identifiant incrémentiel dans la table de courrier électronique, modifiez le paramètre JOIN de la manière suivante:

 LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.id < next.id
 

Answer 3

Faites un GROUP BY après le ORDER BY en encapsulant votre requête avec le GROUP BY comme ceci:

 SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from
 

Answer 4

Selon le standard SQL, vous ne pouvez pas utiliser de colonnes non agrégées dans la liste de sélection. MySQL autorise une telle utilisation (mode sans utilisation ONLY_FULL_GROUP_BY utilisé), mais le résultat n’est pas prévisible.

ONLY_FULL_GROUP_BY

Vous devez d'abord sélectionner parmi Email, MIN (lire), puis avec une seconde requête (ou sous-requête) - Objet.

Answer 5

J'ai du mal avec ces deux approches pour des requêtes plus complexes que ceux indiqués, parce que la sous-requête approche a été horriblement ineficient n'importe quel index j'ai mis, et parce que je n'arrivais pas à l'extérieur de l'auto-jointure par Hibernate

La meilleure (et la plus simple façon de le faire est de groupe par quelque chose qui est construit pour contenir une concaténation des champs dont vous avez besoin et ensuite de les sortir à l'aide d'expressions dans la clause SELECT. Si vous avez besoin de faire un MAX() assurez-vous que le champ que vous souhaitez MAX() est toujours la plus importante à la fin de la concaténation de l'entité.

La clé de la compréhension de ce que la requête n'a de sens que si ces autres champs sont invariant pour toute entité qui satisfait aux Max(), donc, en termes de la sorte les autres pièces de la concaténation peut être ignoré. Il explique comment le faire tout en bas de ce lien. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Si vous pouvez obtenir suis insertion/mise à jour de l'événement (comme un déclencheur) pour pré-calculer la concaténation des champs que vous pouvez indexer et que la requête doit être aussi rapide que si le groupe était plus simplement le domaine que vous aviez à MAX(). Vous pouvez même l'utiliser pour obtenir le maximum de champs multiples. Je l'utilise pour faire des requêtes multi-dimensionnelle des arbres exprimé comme imbriquée ensembles.