Discussion :

[meritena]

Je suis entrain de travailler sur une table avec des pays et des villes.
Chaque ville a une population.
Comment sélectionner les 5 villes les plus peuplées de chaque pays en une requête.

Merci d'avance pour votre aide

[punkoff]

bonjour,

Pour vous aidez, aidez nous d'abord http://www.developpez.net/forums/a69...gage-sql-lire/

[hazamor]

tu n'a pas donné la structure de table ni le SGBD, mais tu peut s'aider de cette requête (avec mysql):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select v.NomVille
from Ville v
Order By v.population desc
limit 0,5

[hazamor]

les 5 villes les plus peuplées de chaque pays en une requête.

Excusez moi, j'ai pas bien vu cette partie, donc la requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select pays, ville
from ville v1
where ville in ( select v2.ville
                  from ville v2
                  where v2.pays=v1.pays
                  order by population desc
                  limit 0,5)

[meritena]

Pour compléter ma demande voici quelques informations complémentaires, j'aimerais effectuer cette requête sur une version de mysql 5.1.43.

Voici une base de données exemple pour mon cas:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
CREATE TABLE `villes` (
  `id_pays` int(11) NOT NULL,
  `population` int(11) NOT NULL,
  `nom` varchar(255) NOT NULL,
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
);
 
--
-- Contenu de la table `villes`
--
 
INSERT INTO `villes` (`id_pays`, `population`, `nom`, `id`) VALUES
(1, 12312, 'ville1', 1),
(2, 22312, 'ville2', 2);
(1, 19312, 'ville3', 3),
(2, 22712, 'ville4', 4);
(1, 124412, 'ville5', 5),
(1, 223312, 'ville6', 6);
(1, 12992, 'ville7', 7),
(2, 223782, 'ville8', 8);
(2, 123112, 'ville9', 9),
(2, 223132, 'ville10', 10);
(2, 123162, 'ville11', 11),
(1, 223111, 'ville12', 12);

[meritena]

Merci hazamor cepedant ma version de mysql répond qu'il n'est pas possible d'utiliser un LIMIT dans un sous-requête de type IN

[punkoff]

Le problème étant que mysql est super limité pour traiter ce genre de demande :s

Si vous arrivez à adapter certaine requête de cet article vous pourriez vous en sortir (en particulier l'exemple 2) :
http://sqlpro.developpez.com/article...clause-window/

[hazamor]

essayez avec cette requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT pays, ville
FROM ville v1
WHERE ville IN ( SELECT v2.ville
                  FROM ville v2
                  WHERE v2.pays=v1.pays
                  and 5<(select count(*)
                            from ville v3
                              where v2.pays=v3.pays 
                              and v2.ville<>v3.ville
                               and v3.population <v2.population))

[SQLpro]

Cette requêtes ne garantie rien, car elle ne prend pas en compte les villes ex aequo, ce qui va introduire des décalages.

Le seule façon de faire est avec RANK ou DENSE_RANK que MySQL ne supporte pas !

A +

[hazamor]

car elle ne prend pas en compte les villes ex aequo

J'ai pas compris, est ce que tu peux m'expliquer svp?

[K2R400]

J'ai pas compris, est ce que tu peux m'expliquer svp?

Si la 5ème a une population de 100 et la 6ème aussi, laquelle des deux villes il faut prendre ???? si on demande de prendre les 5 premières ?

Il faut donc travailler avec du RANK() ou du DENSE_RANK() pour pouvoir gérer
ces égalités.

[SQLpro]

J'ai pas compris, est ce que tu peux m'expliquer svp?

Voici deux exemples ou votre requêtes est en échec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE villes 
( id           int          NOT NULL PRIMARY KEY,
  id_pays      int          NOT NULL,
  population   int          NOT NULL,
  nom          varchar(255) NOT NULL);

--> jeu d'essais n°1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO villes 
VALUES (1,  1, 2200, 'Paris'),
       (2,  1,  850, 'Marseille'),
       (3,  1,  450, 'Lyon'),
       (4,  1,  450, 'Toulouse'),
       (5,  1,  350, 'Nice'),
       (6,  1,  300, 'Nantes'),
       (7,  1,  250, 'Strasbourg'),        
       (8,  1,  250, 'Montpellier'),        
       (9,  1,  250, 'Bordeaux'),
       (10, 1,  250, 'Lille'),
       (11, 1,  200, 'Rennes'),
       (12, 1,  200, 'Reims'),
       (13, 1,  150, 'Le Havre');

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT id_pays, nom, population
FROM   villes v1
WHERE  nom IN (SELECT v2.nom
               FROM   villes v2
               WHERE  v2.id_pays = v1.id_pays
                 AND  5 < (SELECT count(*)
                           FROM   villes v3
                           WHERE  v2.id_pays = v3.id_pays 
                             AND  v2.nom <> v3.nom
                              AND v3.population < v2.population))     
ORDER  BY 3 DESC

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
id_pays     nom                      population
----------- ------------------------ -----------
1           Paris                    2200
1           Marseille                850
1           Lyon                     450
1           Toulouse                 450
1           Nice                     350
1           Nantes                   300

--> vous sortez 6 villes !!!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DELETE FROM villes

--> jeu d'essais n°2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO villes 
VALUES (1,  1, 2200, 'Paris'),
       (2,  1,  850, 'Marseille'),
       (3,  1,  800, 'Lyon'),
       (4,  1,  800, 'Toulouse'),
       (5,  1,  800, 'Nice'),
       (6,  1,  800, 'Nantes'),
       (7,  1,  800, 'Strasbourg'),        
       (8,  1,  250, 'Montpellier'),        
       (9,  1,  250, 'Bordeaux'),
       (10, 1,  250, 'Lille'),
       (11, 1,  200, 'Rennes'),
       (12, 1,  200, 'Reims'),
       (13, 1,  150, 'Le Havre');

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT id_pays, nom, population
FROM   villes v1
WHERE  nom IN (SELECT v2.nom
               FROM   villes v2
               WHERE  v2.id_pays = v1.id_pays
                 AND  5 < (SELECT count(*)
                           FROM   villes v3
                           WHERE  v2.id_pays = v3.id_pays 
                             AND  v2.nom <> v3.nom
                              AND v3.population < v2.population))     
ORDER  BY 3 DESC

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
id_pays     nom                        population
----------- -------------------------- -----------
1           Paris                      2200
1           Marseille                  850

--> vous en oubliez 3 !!!

--> solutions avec RANK() :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
WITH T AS
(SELECT *, RANK() OVER(PARTITION BY id_pays ORDER BY population DESC) AS RANG
 FROM   villes)
SELECT id_pays, nom, population 
FROM   T 
WHERE  RANG <= 5

-- jeu d'essais n°1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
id_pays     nom                    population
----------- ---------------------- -----------
1           Paris                  2200
1           Marseille              850
1           Lyon                   450
1           Toulouse               450
1           Nice                   350

-- jeu d'essais n°2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
id_pays     nom                      population
----------- ------------------------ -----------
1           Paris                    2200
1           Marseille                850
1           Lyon                     800
1           Toulouse                 800
1           Nice                     800
1           Nantes                   800
1           Strasbourg               800

--> solutions avec DENSE_RANK() :

--> jeu d'essais n°1
id_pays nom population

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
----------- ------------------ -----------
1           Paris              2200
1           Marseille          850
1           Lyon               450
1           Toulouse           450
1           Nice               350
1           Nantes             300

-- jeu d'essais n°2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
id_pays     nom                   population
----------- --------------------- -----------
1           Paris                 2200
1           Marseille             850
1           Lyon                  800
1           Toulouse              800
1           Nice                  800
1           Nantes                800
1           Strasbourg            800
1           Montpellier           250
1           Bordeaux              250
1           Lille                 250
1           Rennes                200
1           Reims                 200

A +