MySQL: Retour des champs où COUNT(*) est plus grande que

J'ai le SQL suivant, mais je ne veux renvoyer les lignes où les "hits" sont plus grands que 10.

SELECT clicks.affiliate, COUNT(*) AS hits, affiliates.title, affiliates.url
FROM clicks
INNER JOIN affiliates ON affiliates.id = clicks.affiliate
GROUP BY clicks.affiliate

Grâce.

OriginalL'auteur Andre Backlund | 2010-10-18

  1. 20

    Filtrer par une globale, vous devez utiliser le having clause. Contrairement à de nombreux Sgbdr MySQL vous permet d'utiliser l'alias de colonne dans ce contexte (la Plupart des autres Sgbdr voudrais également insister sur affiliates.title, affiliates.url être ajouté à la group by clause de)

    SELECT clicks.affiliate, COUNT(*) AS hits, affiliates.title, affiliates.url
FROM clicks
INNER JOIN affiliates ON affiliates.id = clicks.affiliate
GROUP BY clicks.affiliate
HAVING hits > 10

    OriginalL'auteur Martin Smith

  2. 3
    SELECT clicks.affiliate, COUNT(*) AS hits, affiliates.title, affiliates.url
FROM clicks
INNER JOIN affiliates ON affiliates.id = clicks.affiliate
GROUP BY clicks.affiliate
HAVING COUNT(*) > 10

    OriginalL'auteur klaus

  3. 0
     ...
HAVING hits > 10

    OriginalL'auteur Ignacio Vazquez-Abrams