[Résolu][MySQL]Fusion de requêtes...

10 août 2007, 13:30

Hello !
Je suis entrain de refaire un peu de code pour un site de pronostics sur la Ligue 1 entre potes.
J'ai généré un tableau (via PHP et MySQL) qui liste les éléments ci-dessous en image.

Actuellement je fais 6 requêtes MySQL pour générer 1 ligne par équipe qui sont les suivantes:

$sql_VDom="SELECT count(*) AS total, score_team1 AS ButsPour, score_team2 AS ButsContre FROM prono_matchs WHERE team1='".$data['team_abbr']."' AND score_team1>score_team2 AND score_team1<>'' AND score_team2<>'' GROUP BY ButsPour, ButsContre";
	$sql_VExt="SELECT count(*) AS total, score_team1 AS ButsContre, score_team2 AS ButsPour FROM prono_matchs WHERE team2='".$data['team_abbr']."' AND score_team1<score_team2 AND score_team1<>'' AND score_team2<>'' GROUP BY ButsPour, ButsContre";
	//Défaîtes
	$sql_DDom="SELECT count(*) AS total, score_team1 AS ButsPour, score_team2 AS ButsContre FROM prono_matchs WHERE team1='".$data['team_abbr']."' AND score_team1<score_team2 AND score_team1<>'' AND score_team2<>'' GROUP BY ButsPour, ButsContre";
	$sql_DExt="SELECT count(*) AS total, score_team1 AS ButsContre, score_team2 AS ButsPour FROM prono_matchs WHERE team2='".$data['team_abbr']."' AND score_team1>score_team2 AND score_team1<>'' AND score_team2<>'' GROUP BY ButsPour, ButsContre";
	//Nuls
	$sql_NDom="SELECT count(*) AS total, score_team1 AS ButsPour, score_team2 AS ButsContre FROM prono_matchs WHERE team1='".$data['team_abbr']."' AND score_team1=score_team2 AND score_team1<>'' AND score_team2<>'' GROUP BY ButsPour, ButsContre";
	$sql_NExt="SELECT count(*) AS total, score_team1 AS ButsContre, score_team2 AS ButsPour FROM prono_matchs WHERE team2='".$data['team_abbr']."' AND score_team1=score_team2 AND score_team1<>'' AND score_team2<>'' GROUP BY ButsPour, ButsContre";

La comparaison score_team1<>'' et score_team2<>'' est faite car je stocke toutes les journées dans la même table, donc certains matchs n'ont pas encore eu lieu.
La variable $data['team_abbr'] provient d'une autre requête pour individualiser celle-ci, rien d'extra

Voilà une image de ma table MySQL.

Je me demandais si il n'y avait pas moyen de mettre tout ça dans une seule requête et de générer une réponse ressemblant à mon tableau en HTML...
Ca éviterait des opérations en PHP

10 août 2007, 14:09

Personnellement, j'aurais fait ça :

Code : Tout sélectionner

SELECT VDom.c, VExt.c, NDom.c, NExt.c, DDom.c, DExt.c, (VDom.sc1+VExt.sc2+NDom.sc1+NExt.sc2+DDom.sc1+DExt.sc2) AS P, (VDom.sc2+VExt.sc1+NDom.sc2+NExt.sc1+DDom.sc2+DExt.sc1) AS C
FROM
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1>score_team2 AND team1='team') AS VDom,
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1<score_team2 AND team2='team') AS VExt,
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1=score_team2 AND team1='team') AS NDom,
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1=score_team2 AND team2='team') AS NExt,
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1<score_team2 AND team1='team') AS DDom,
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1>score_team2 AND team2='team') AS DExt

Ca te fait une requète par équipe. Mais on peut encore réduire à une requète pour toutes les équipes.

10 août 2007, 14:23

La deuxième possibilité à laquelle j'ai pensé, c'est ça :

Code : Tout sélectionner

SELECT list.nom, VDom.c, VExt.c, NDom.c, NExt.c, DDom.c, DExt.c, (VDom.sc1+VExt.sc2+NDom.sc1+NExt.sc2+DDom.sc1+DExt.sc2) AS P, (VDom.sc2+VExt.sc1+NDom.sc2+NExt.sc1+DDom.sc2+DExt.sc1) AS C, (P-C) AS Diff, (VDom.c*3+VExt.c*3+NDom.c+NExt.c) AS points
FROM
	(SELECT nom FROM teams ORDER BY nom),
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1>score_team2
	ORDER BY team1) AS VDom,
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1<score_team2
	ORDER BY team2) AS VExt,
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1=score_team2
	ORDER BY team1) AS NDom,
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1=score_team2
	ORDER BY team2) AS NExt,
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1<score_team2
	ORDER BY team1) AS DDom,
	(SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c
	FROM prono_matchs
	WHERE score_team1>score_team2
	ORDER BY team2) AS DExt
ORDER BY points DESC, Diff DESC, P DESC, C ASC, list.nom ASC

Normalement, ça te donne ton classement sans traitement derrière, et en une seule requète, mais il faut que tu crées une table avec la liste des noms des équipes, parce que la requète en a besoin pour aligner les résultats.

10 août 2007, 14:24

ouch !
Merci, je vais décortiquer ça

Ce matin la tête dans le guidon j'avais créé une requête avec des alias de tables et des alias tout court... j'ai claqué 35 fois mon serveur MySQL

Merci !!

10 août 2007, 14:30

J'ai des petites erreurs sur la deuxième requète.Il faut faire des jointures, et non des produits carthésiens. Je vais modifier ca.

10 août 2007, 14:36

Code : Tout sélectionner

SELECT
   list.nom, VDom.c, VExt.c, NDom.c, NExt.c, DDom.c, DExt.c,
   (VDom.sc1+VExt.sc2+NDom.sc1+NExt.sc2+DDom.sc1+DExt.sc2) AS P,
   (VDom.sc2+VExt.sc1+NDom.sc2+NExt.sc1+DDom.sc2+DExt.sc1) AS C,
   (P-C) AS Diff,
   (VDom.c*3+VExt.c*3+NDom.c+NExt.c) AS points 
FROM 
   (SELECT nom FROM teams ORDER BY nom)
   LEFT JOIN (SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c 
   FROM prono_matchs 
   WHERE score_team1>score_team2) AS VDom ON list.nom=VDom.team1
   LEFT JOIN (SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c 
   FROM prono_matchs 
   WHERE score_team1<score_team2) AS VExt ON list.nom=VExt.team2
   LEFT JOIN (SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c 
   FROM prono_matchs 
   WHERE score_team1=score_team2) AS NDom ON list.nom=NDom.team1
   LEFT JOIN (SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c 
   FROM prono_matchs 
   WHERE score_team1=score_team2) AS NExt ON list.nom=NExt.team2
   LEFT JOIN (SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c 
   FROM prono_matchs 
   WHERE score_team1<score_team2) AS DDom ON list.nom=DDom.team1
   LEFT JOIN (SELECT SUM(score_team1) AS sc1, SUM(score_team2) AS sc2, COUNT(*) AS c 
   FROM prono_matchs 
   WHERE score_team1>score_team2) AS DExt ON list.nom=DExt.team2
ORDER BY
   points DESC,
   Diff DESC,
   P DESC,
   C ASC,
   list.nom ASC

C'est mieux avec des LEFT JOIN

10 août 2007, 16:09

Et bé...
Je vais demander à ma copine une explication au ralenti vu que c'est son job les bases de données

J'ai une erreur par contre, qui me dit que chaque table dérivé doit avoir un alias...
Je retourne le truc dans tous les sens sans trouver la soluce (entre 2 appels téléphoniques c'est pratique !)

Merci en tout cas du coup de main !
Je mettrais peut être mon script total en téléchargement un de ces 4, on fait des pronos sur les scores, avec un classement basé sur les bons pronos et des bonifications sur le score.
Sympatoche entre potes

J'ai une table prono_teams qui contient la colonne team_abbr, me servant pour repérer les équipes (j'aurais pu mettre des chiffres mais c'est moins causant pour une modif à la main !)

Edit: J'ai un peu modifier la requête pour coller à ma base et faire le tri sur le nom des équipes, mais ça coince au fur et à mesure...

Code : Tout sélectionner

 SELECT list.team_abbr, list.team, VDom.c, VExt.c, NDom.c, NExt.c, DDom.c, DExt.c, 
    (VDom.sc1+VExt.sc2+NDom.sc1+NExt.sc2+DDom.sc1+DExt.sc2) AS P,
   (VDom.sc2+VExt.sc1+NDom.sc2+NExt.sc1+DDom.sc2+DExt.sc1) AS C,
   (P-C) AS Diff,
 (VDom.c *3 + VExt.c *3 + NDom.c + NExt.c) AS points
FROM (

SELECT team_abbr, team
FROM prono_teams
ORDER BY team
) AS list
LEFT JOIN (

SELECT team1, SUM( score_team1 ) AS sc1, SUM( score_team2 ) AS sc2, COUNT( * ) AS c
FROM prono_matchs
WHERE score_team1 > score_team2
AND score_team1 <> ''
GROUP BY team1
) AS VDom ON list.team_abbr = VDom.team1
LEFT JOIN (

SELECT team2, SUM( score_team1 ) AS sc1, SUM( score_team2 ) AS sc2, COUNT( * ) AS c
FROM prono_matchs
WHERE score_team1 < score_team2
AND score_team1 <> '' 
GROUP BY team2
) AS VExt ON list.team_abbr = VExt.team2
LEFT JOIN (

SELECT team1, SUM( score_team1 ) AS sc1, SUM( score_team2 ) AS sc2, COUNT( * ) AS c
FROM prono_matchs
WHERE score_team1 = score_team2
AND score_team1 <> ''
GROUP BY team1
) AS NDom ON list.team_abbr = NDom.team1
LEFT JOIN (

SELECT team2, SUM( score_team1 ) AS sc1, SUM( score_team2 ) AS sc2, COUNT( * ) AS c
FROM prono_matchs
WHERE score_team1 = score_team2
AND score_team1 <> ''
GROUP BY team2
) AS NExt ON list.team_abbr = NExt.team2
LEFT JOIN (

SELECT team1,SUM( score_team1 ) AS sc1, SUM( score_team2 ) AS sc2, COUNT( * ) AS c
FROM prono_matchs
WHERE score_team1 < score_team2
AND score_team1 <> ''
GROUP BY team1
) AS DDom ON list.team_abbr = DDom.team1
LEFT JOIN (

SELECT team2, SUM( score_team1 ) AS sc1, SUM( score_team2 ) AS sc2, COUNT( * ) AS c
FROM prono_matchs
WHERE score_team1 > score_team2
AND score_team1 <> ''
GROUP BY team2
) AS DExt ON list.team_abbr = DExt.team2
ORDER BY
   points DESC,
   Diff DESC,
   P DESC,
   C ASC,
   list.team ASC

Voilà ma table prono_teams

Edit à 16h32: ça colle plus à ce que je veux, par contre il me dit que P n'existe pas dans les champs...
Si je l'enlève, avec les buts Contre et la différence de buts, ça passe.

Voilà ma structure de table avec les données

Code : Tout sélectionner

-- Structure de la table `prono_matchs`
-- 

CREATE TABLE `prono_matchs` (
  `id_match` tinyint(4) NOT NULL auto_increment,
  `journee` tinyint(4) NOT NULL,
  `team1` text collate latin1_general_ci NOT NULL,
  `team2` text collate latin1_general_ci NOT NULL,
  `score_team1` text collate latin1_general_ci NOT NULL,
  `score_team2` text collate latin1_general_ci NOT NULL,
  `date` text collate latin1_general_ci NOT NULL,
  UNIQUE KEY `id_match` (`id_match`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=31 ;

-- 
-- Contenu de la table `prono_matchs`
-- 

INSERT INTO `prono_matchs` VALUES (1, 1, 'bordeaux', 'rcl', '1', '0', '1186250400');
INSERT INTO `prono_matchs` VALUES (2, 1, 'smc', 'ogcn', '1', '0', '1186250400');
INSERT INTO `prono_matchs` VALUES (3, 1, 'muc', 'metz', '1', '0', '1186329600');
INSERT INTO `prono_matchs` VALUES (4, 1, 'losc', 'lorient', '0', '0', '1186250400');
INSERT INTO `prono_matchs` VALUES (5, 1, 'ol', 'aja', '2', '0', '1186340400');
INSERT INTO `prono_matchs` VALUES (6, 1, 'asm', 'asse', '1', '1', '1186250400');
INSERT INTO `prono_matchs` VALUES (7, 1, 'psg', 'sochaux', '0', '0', '1186250400');
INSERT INTO `prono_matchs` VALUES (8, 1, 'sr', 'asnl', '0', '2', '1186250400');
INSERT INTO `prono_matchs` VALUES (9, 1, 'rcs', 'om', '0', '0', '1186240200');
INSERT INTO `prono_matchs` VALUES (10, 1, 'va', 'tfc', '3', '1', '1186250400');
INSERT INTO `prono_matchs` VALUES (11, 2, 'tfc', 'ol', '', '', '1186845000');
INSERT INTO `prono_matchs` VALUES (12, 2, 'asnl', 'smc', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (13, 2, 'lorient', 'asm', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (14, 2, 'om', 'sr', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (15, 2, 'sochaux', 'muc', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (16, 2, 'aja', 'bordeaux', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (17, 2, 'ogcn', 'rcs', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (18, 2, 'asse', 'va', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (19, 2, 'metz', 'losc', '', '', '1186934400');
INSERT INTO `prono_matchs` VALUES (20, 2, 'rcl', 'psg', '', '', '1186941600');
INSERT INTO `prono_matchs` VALUES (21, 3, 'tfc', 'ol', '', '', '1186845000');
INSERT INTO `prono_matchs` VALUES (22, 3, 'aja', 'bordeaux', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (23, 3, 'lorient', 'asm', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (24, 3, 'om', 'sr', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (25, 3, 'asnl', 'smc', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (26, 3, 'ogcn', 'rcs', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (27, 3, 'asse', 'va', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (28, 3, 'sochaux', 'muc', '', '', '1186855200');
INSERT INTO `prono_matchs` VALUES (29, 3, 'metz', 'losc', '', '', '1186934400');
INSERT INTO `prono_matchs` VALUES (30, 3, 'rcl', 'psg', '', '', '1186941600');

-- --------------------------------------------------------

-- 
-- Structure de la table `prono_teams`
-- 

CREATE TABLE `prono_teams` (
  `id` tinyint(4) NOT NULL auto_increment,
  `team` text collate latin1_general_ci NOT NULL,
  `team_abbr` text collate latin1_general_ci NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=21 ;

-- 
-- Contenu de la table `prono_teams`
-- 

INSERT INTO `prono_teams` VALUES (1, 'Marseille', 'om');
INSERT INTO `prono_teams` VALUES (2, 'Nancy', 'asnl');
INSERT INTO `prono_teams` VALUES (3, 'Lens', 'rcl');
INSERT INTO `prono_teams` VALUES (4, 'Sochaux', 'sochaux');
INSERT INTO `prono_teams` VALUES (5, 'Lorient', 'lorient');
INSERT INTO `prono_teams` VALUES (6, 'Toulouse', 'tfc');
INSERT INTO `prono_teams` VALUES (7, 'Strasbourg', 'rcs');
INSERT INTO `prono_teams` VALUES (8, 'Rennes', 'sr');
INSERT INTO `prono_teams` VALUES (9, 'Bordeaux', 'bordeaux');
INSERT INTO `prono_teams` VALUES (10, 'Metz', 'metz');
INSERT INTO `prono_teams` VALUES (11, 'Lyon', 'ol');
INSERT INTO `prono_teams` VALUES (12, 'Monaco', 'asm');
INSERT INTO `prono_teams` VALUES (13, 'Le Mans', 'muc');
INSERT INTO `prono_teams` VALUES (14, 'Caen', 'smc');
INSERT INTO `prono_teams` VALUES (15, 'Lille', 'losc');
INSERT INTO `prono_teams` VALUES (16, 'Auxerre', 'aja');
INSERT INTO `prono_teams` VALUES (17, 'Paris', 'psg');
INSERT INTO `prono_teams` VALUES (18, 'Saint Etienne', 'asse');
INSERT INTO `prono_teams` VALUES (19, 'Valenciennes', 'va');
INSERT INTO `prono_teams` VALUES (20, 'Nice', 'ogcn');

10 août 2007, 16:52

Edit à 16h32: ça colle plus à ce que je veux, par contre il me dit que P n'existe pas dans les champs...

Essaye de remplacer P et C par leur expression respective dans le select lorsque tu calcules la différence de buts.

10 août 2007, 16:54

Visiblement ça coince, j'ai des valeurs NULL dans mes réponses...
Est-ce que ça peut venir que certaines requêtes ne donnent aucune réponses pour le moment ?
Genre pas de match nul, pas de défaîtes...
Du coup MySQL retournerait un "pas d'enregistrement" au lieu de 0

10 août 2007, 17:03

Visiblement ça coince, j'ai des valeurs NULL dans mes réponses...
Est-ce que ça peut venir que certaines requêtes ne donnent aucune réponses pour le moment ?
Genre pas de match nul, pas de défaîtes...
Du coup MySQL retournerait un "pas d'enregistrement" au lieu de 0

C'est exactement cela. Certaines équipes n'ont pas fait de nul à l'exttérieur, ou pas de défaite à domicile.
Avec un JOIN, MySQL aurait ignoré la ligne, ce qui aurait été génant. En mettant un LEFT JOIN, s'il ne trouve aucun enregistrement pour la jointure, il la fait quand même, et vu qu'il lui manque des données, il renvoie NULL, c'est à dire la valeur classique pour dire que la donnée est indéfinie.
Si ca influence sur le résultat des calculs (notamment de points ou de buts), tu peux utiliser la fonction IFNULL().
Si tu veux qu'une valeur soit de 0 si elle est nulle, tu fais :

Code : Tout sélectionner

IFNULL(var, 0)

Ca renverra var si var n'est pas indéfini, et 0 dans le cas contraire.

10 août 2007, 17:04

Je vais demander à ma copine une explication au ralenti vu que c'est son job les bases de données

Elle doit être malheureuse que tu sois un fana de foot

10 août 2007, 17:22

Elle doit être malheureuse que tu sois un fana de foot

Bah, les matchs ne la dérange pas trop... c'est surtout le squat de la TV quand je joue à PES6 qui la gonfle

Super merci !
Le IFNULL marche impeccable !
Je n'ai plus qu'à faire apparaître ce que je veux et bingo !

Pour le P et le C, j'ai eu le cas l'autre jour, MySQL a du mal avec un alias qu'on vient de créer...
Ca marche en mettant les valeurs.

10 août 2007, 17:46

Elle doit être malheureuse que tu sois un fana de foot
Bah, les matchs ne la dérange pas trop... c'est surtout le squat de la TV quand je joue à PES6 qui la gonfle

leaule

Super merci !
Le IFNULL marche impeccable !
Je n'ai plus qu'à faire apparaître ce que je veux et bingo !

Hé bien je suis ravi pour toi, tu n'as plus qu'à mettre [Résolu]

10 août 2007, 18:01

Tain, tellement content que ça fonctionne, j'étais reparti tête baissé dans le PHP.
Merci de ton aide en tout cas

[Résolu][MySQL]Fusion de requêtes...

Qui est en ligne