J'ai un problème avec l'utilisation de group by et la participation à la même requête. (J'utilise world DB dans MySQL, juste deux tables. Premier - pays, deuxième - villes). Je veux avoir la plus grande ville de chaque continent. Voici ce que j'ai essayé

SELECT
    k.Continent,
    m.name,
    MAX(m.Population)
FROM
    city m
        JOIN
    country k ON m.CountryCode = k.Code
GROUP BY 1;

J'obtiens de bonnes valeurs dans la colonne population et continent mais les noms de villes sont erronés. Ce n'est pas la ville la plus peuplée mais la première ville de chaque continent de la table.

3
iknow 21 avril 2020 à 13:25

2 réponses

Meilleure réponse

Il s'agit d'un problème majeur par groupe. Vous souhaitez filtrer plutôt un agrégat.

Vous pouvez utiliser une sous-requête corrélée pour cela:

select co.continent, ci.name, ci.population
from city ci
inner join country co where co.code = ci.countryCode
where ci.population = (
    select max(ci1.population)
    from city ci1
    inner join country co1 on co1.code = ci1.countryCode
    where co1.continent = co.continent
)

Si vous avez la chance d'exécuter MySQL 8.0, il est plus simple d'utiliser les fonctions de fenêtre:

select *
from (
    select 
        co.continent, 
        ci.name, 
        ci.population, 
        rank() over(partition by co.continent order by ci.population desc) rn
    from city ci
    inner join country co where co.code = ci.countryCode
) t
where rn = 1
3
GMB 21 avril 2020 à 10:30

La meilleure façon de répondre à la question est probablement d'utiliser les fonctions de fenêtre, row_number():

SELECT Continent, name, Population
FROM (SELECT co.Continent, ci.name, ci.Population,
             ROW_NUMBER() OVER (PARTITION BY co.Continent ORDER BY ci.Population DESC) as seqnum
      FROM city ci JOIn
           country co
           ON ci.CountryCode = co.Code
     ) cc
WHERE seqnum = 1
2
Gordon Linoff 21 avril 2020 à 10:33