Sous-requête

Par GreenWitch, il y a 8 ans

Base de données MySQL

Bonjour,

Je ne sais pas comment faire une requête dans une autre requête. Je m'explique.
J'ai cette requête

$req = $bdd->prepare('SELECT ville_nom, ville_population, ville_code_commune, ville_departement, dep_nom FROM villes INNER JOIN departements ON villes.ville_departement = departements.dep_code WHERE dep_region = :region AND ville_nom NOT LIKE "%Arrondissement" ORDER BY ville_population DESC LIMIT 100');

qui me permet de sélectionner les 100 premières villes d'une région classées par population.
Maintenant j'aimerais classer ces 100 villes par ordre alphabétique mais je ne sais pas comment faire.
Merci de votre aide.

27 réponses

Huggy, il y a 8 ans

tu pourrais essayer ceci

SELECT ville_nom, ville_population, ville_code_commune, ville_departement FROM villes WHERE FIND_IN_SET(ville_code_commune,( SELECT SUBSTRING_INDEX(GROUP_CONCAT(ville_code_commune ORDER BY ville_population DESC SEPARATOR ','), ',' ,100) FROM villes INNER JOIN departements ON villes.ville_departement = departements.dep_code WHERE dep_region = :region AND ville_nom NOT LIKE "%Arrondissement" GROUP BY dep_region))

GROUP_CONCAT crée une liste triée à partir des valeurs du GROUP BY (ici par région)
donc une liste des code_commune trié par population décroissante
SUBSTRING_INDEX coupe cette liste aux 100 premières valeurs
FIND_IN_SET va sortir tous les code_commune retournés dans les listes de la sous-requête

Je n'ai pas testé et je ne sais pas si avec 2 à 3000 communes par région, ça plante ou pas ?

Edit : pour booster la taille des listes (par défaut 1024 caractères)
SET @@group_concat_max_len = 1000000;

Pierrot01, il y a 8 ans

salut,

$req = $bdd->prepare('SELECT ville_nom, ville_population, ville_code_commune, ville_departement, dep_nom FROM villes INNER JOIN departements ON villes.ville_departement = departements.dep_code WHERE dep_region = :region AND ville_nom NOT LIKE "%Arrondissement" ORDER BY ville_nom,ville_population DESC LIMIT 100');

aussi simple que ça ;)

@plus

Pierre

Huggy, il y a 8 ans

@Pierrot01 ça trie par nom et ça prend les 100 premieres
le tri de deuxième ordre n'intervient que pour les villes de même nom !!!

Pierrot01, il y a 8 ans

C'est vrai, j'ai été un peu vite :D :D
mais en lisant cette requetète, je ne vois que vvvviiiiiiiiiiiiiiiiiiiiiiiiiiiiiiilllllllllllllllllllllllllllllllllllllleeeeeeeeeeeeeeeeeeeee.
champ très mal nommé :D :D
alors, pour évité le mal de tête, j'irais pas plus loin :D :D

sinon, juste une idée,
il fait, select les noms des villes par ordre alpha where id in (sa première requete)

@plus

Pierre

@plus.
Pierre

Huggy, il y a 8 ans

ça serait simple si le LIMIT marchait dans les sous-requete mais LIMIT n'est appliqué qu'à la fin du traitement
on ferait une sous-requete qui tri par population et qui en garde 100
puis une requête principale qui trie par nom
mais non
d'où l'horrible bidouille du GROUP_CONCAT
on doit pouvoir s'en sortir aussi avec une table temporaire

Pierrot01, il y a 8 ans

heuuu, le limit fonctionne dans les sous requete :D :D

comme ça :

select * FROM table WHERE id IN (SELECT * FROM (SELECT id FROM table LIMIT 0,200) AS temp) limit UnPeuOuBeaucoup

@plus

Pierre

Huggy, il y a 8 ans

Non ça marche pas avec les opérateurs IN / ALL / ANY / SOME
voir la doc de la version 5.7

MySQL does not support LIMIT in subqueries for certain subquery operators:

mysql> SELECT * FROM t1 -> WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1); ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
GreenWitch, il y a 8 ans

Bonsoir et merci à vous deux pour votre aide.
Je testerai demain vos codes et vous dirai ce qu'il en ressort.
Encore merci et bonne soirée :-)

Pierrot01, il y a 8 ans

je suis en version 5.6 mais si je change de version je ferais attention ;)
@plus
Pierre

Pierrot01, il y a 8 ans

@Huggy,

ma requete fonctionne
un LIMIT dans une requete IN fonctionne.
relis bien la doc et relis aussi ma requète.
pour preuve :

@plus
Pierre

Pierrot01, il y a 8 ans

j'ai voulu pousser un peu

voila les 100 premiers dossiers des 2 premiers commerciaux avec un limit dans une sous requete
et ça fonctionne très bien sous mysql 7 ;)
il sufiit de faire une table dérivée.
comme ça, MySql créeer une table temporaire.

@plus

Pierre

Pierrot01, il y a 8 ans

Salut,
Tiens, voilà ta requete :

SELECT ville_id, ville_nom, ville_population, ville_code_commune, ville_departement, dep_nom FROM villes WHERE ville_id IN ( SELECT ville_id FROM( SELECT distinct ville_id FROM villes INNER JOIN departements ON villes.ville_departement = departements.dep_code WHERE dep_region = region AND ville_nom NOT LIKE "%Arrondissement" ORDER BY ville_population DESC LIMIT 100) as temp) ORDER BY ville_nom;

@plus

Pierre

Huggy, il y a 8 ans

donc le fait d'éliminer le IN en intercalant une sous-requête ça fonctionne
merci pour l'astuce

En utilisant directement la sous-requete (celle avec le limit) dans une jointure, on supprime aussi le IN

Pierrot01, il y a 8 ans

en fait pas de in select table mais un in select xx from (select ....................
cà créer une table temproraire.
justement, il l'explique un peut plus loin dans le lien que tu m'as passé ;)

@plus
Pierre

Huggy, il y a 8 ans

avec une jointure ça donne un truc comme ça
du coup je récupère le nom du département dans la sous-requête

SELECT V.ville_code_commune, V.ville_nom, V.ville_population, V.ville_departement, temp.dep_nom FROM villes V INNER JOIN ( SELECT ville_code_commune, dep_nom FROM villes INNER JOIN departements ON villes.ville_departement = departements.dep_code WHERE dep_region = :region AND ville_nom NOT LIKE "%Arrondissement" ORDER BY ville_population DESC LIMIT 100) as temp ON V.ville_code_commune = temp.ville_code_commune ORDER BY V.ville_nom;
GreenWitch, il y a 8 ans

Salut.
@Pierre
J'ai essayé ton code mais cela ne donne rien.
Je ne comprends pas ce que vient faire "ville_id".

@Huggy
J'ai essayé aussi ton code. Même chose cela ne donne rien.
Je ne comprends pas le "V" dans ton code.

En tout cas cela me fait visualiser du code SQL et c'est hyper intérressant car je suis débutant.
Je vais essayé de bidouiller avec vos codes pour voir si j'arrive à quelque chose.
Ce serait bien si vous pouviez m'expliquer un peu vos codes.
Merci beaucoup pour votre coup de main :-) @+

Pierrot01, il y a 8 ans

salut,
lis bien, c'est expliqué ;)

t'as pas de champ id dans ta table ville ?

@plus

Pierre

GreenWitch, il y a 8 ans

@Pierre
Si, j'ai un champs "ville_id", mais ça fonctionne pas chez moi.
Merci quand même. @+

Huggy, il y a 8 ans

Salut GreenWitch
j'ai considéré que code_commune était la clé primaire (code INSEE)
le V c'est un alias de 'villes' c'est pour faire plus court

j'ai testé avec des données d'opendata, ma derniere requête fonctionne (0,04s)
et celle avec GROUP_CONCAT fonctionne aussi (0,4s) avec le même résultat
dit nous ce qui cloche, quel message d'erreur

Pierrot01, il y a 8 ans

ça fonctionne pas ?
c'est à dire ?
ça retourne rien ?....
ça retourne autre chose que ce que tu veux ?
message d'erreur ?

si tu veux qu'on t'aide, faudrat être au moins autant explicite que ta requète ;)

@plus
Pierre

Huggy, il y a 8 ans

J'ai testé pour la région Hauts de France
voici le résultat

villes par nom

Pierrot01, il y a 8 ans

ok, je sais, j'ai pas mes lunettes, mais quand même :D :D

GreenWitch, il y a 8 ans

RE
@Pierre
J'ai testé dans la console SQL de phpMyAdmin, ça m'indique "#1054 - Champ 'dep_nom' inconnu dans field list".

@Huggy
Cela à l'air de fonctionner (je parle de ta seconde requête) mais il faut que je vérifie si les résultats sont conformes à la requête.
Je te dis ça une fois que j'ai vérifié.

Huggy, il y a 8 ans

@Pierrot01 j'ai merdé pour coller mon image (hostingpics ferme boutique)
du coup j'ai un lien dropbox mais comment fonctionne ce fichu bouton 'image' ?

GreenWitch, il y a 8 ans

@Huggy
C'est tout bon. Ca fonctionne nickel !
Un grand merci à toi et à Pierre pour votre aide précieuse.
C'est bon d'apprendre. @+

Pierrot01, il y a 8 ans

@GreenWitch
un dernier conseil.
évite de prefixer le nom de tes champs ville dans la table ville :D
sur de longues requètes, tu as 15 à 20 fois le mot ville dans la requète.
ça en devient illisible ;)

@plus

Pierre

GreenWitch, il y a 8 ans

@Pierre
Ah oui c'est vrai que c'est un peu lourd.
Merci du conseil et pour ton aide. Bon après-midi à toi. :-)