Discussion :

[Anandamine]

Bonsoir,

j'ai une bd nommée port, contenant 3 tables.
Les 2 tables concernées ici sont la table visites et la table bateaux.
On peut les visualiser ici:
http://anandamine.hostei.com/tables_...et_bateaux.php
Je désire retouver le nom du bateau qui était le plus souvent présent dans le port.
Pour celà pas de problème, j'effectue la requête ci-dessous.
Le problème se situe dans l'écriture de la requête: je répète 2 x le même bloc d'instructions et je n'arrive pas à la raccourcir même en utilisant les alias...
J'espère que qqn d'assez indulgent et calé pourra m'aider à la réduire de moitié.
Merci.

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
SELECT nom_bateau, SUM( nb_jours_bateau ) t
FROM 
(
	SELECT nom_bateau, SUM( TO_DAYS( date_sortie ) - TO_DAYS( date_entree ) ) AS nb_jours_bateau
	FROM bateaux
	INNER JOIN visites ON bateaux.id_bateau = visites.ref_bateau
	WHERE date_sortie IS NOT NULL 
	GROUP BY nom_bateau
	UNION 
	SELECT nom_bateau, SUM( TO_DAYS( NOW( ) ) - TO_DAYS( date_entree ) ) AS nb_jours_bateau_present
	FROM bateaux
	INNER JOIN visites ON bateaux.id_bateau = visites.ref_bateau
	WHERE date_sortie IS NULL 
	GROUP BY nom_bateau
)tt
GROUP BY nom_bateau
HAVING t=
( 
	SELECT MAX( t ) 
	FROM 
	(
		SELECT nom_bateau, SUM( nb_jours_bateau ) t
		FROM 
		(
			SELECT nom_bateau, SUM( TO_DAYS( date_sortie ) - TO_DAYS( date_entree ) ) AS nb_jours_bateau
			FROM bateaux
			INNER JOIN visites ON bateaux.id_bateau = visites.ref_bateau
			WHERE date_sortie IS NOT NULL 
			GROUP BY nom_bateau
			UNION 
			SELECT nom_bateau, SUM( TO_DAYS( NOW( ) ) - TO_DAYS( date_entree ) ) AS nb_jours_bateau_present
			FROM bateaux
			INNER JOIN visites ON bateaux.id_bateau = visites.ref_bateau
			WHERE date_sortie IS NULL 
			GROUP BY nom_bateau
		)tt
	GROUP BY nom_bateau
	)ttt
)

[CinePhil]

Essaie comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT b.nom_bateau,
	SUM(
		CASE 
			WHEN v.date_sortie IS NOT NUL THEN DATEDIFF(v.date_sortie, v.date_entree)
			ELSE DATEDIFF(CURRENT_DATE, v.date_entree)
		END
	) AS nb_jours_bateau
FROM bateaux b
INNER JOIN visites v ON v.ref_bateau = b.id_bateau
GROUP BY b.nom_bateau
HAVING nb_jours_bateau = MAX(nb_jours_bateau)

[Anandamine]

Woaw, solidement épurée la requête...Malheureusement non fonctionnelle
(MySQL n'a retourné aucun enregistrement)
Je ne connaissais pas les case, c'est bien de voir comment les utiliser.
Merci Phil.

[CinePhil]

Je vois qu'il manque un L à mon NULL mais ce n'est pas la cause du problème.

Je craignais d'ailleurs que mon HAVING ne fonctionne pas.

Pour le moment, le je vois pas comment faire autrement que de reprendre presque toute la requête de groupage dans le HAVING, en supprimant seulement la jointure qui y est inutile :

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
SELECT b.nom_bateau,
	SUM(
		CASE 
			WHEN v.date_sortie IS NOT NULL THEN DATEDIFF(v.date_sortie, v.date_entree)
			ELSE DATEDIFF(CURRENT_DATE, v.date_entree)
		END
	) AS nb_jours_bateau
FROM bateaux b
INNER JOIN visites v ON v.ref_bateau = b.id_bateau
GROUP BY b.nom_bateau
HAVING nb_jours_bateau = 
(
	SELECT MAX(nb)
	FROM
	(
		SELECT ref_bateau,
			SUM(
				CASE 
					WHEN date_sortie IS NOT NULL THEN DATEDIFF(date_sortie, date_entree)
					ELSE DATEDIFF(CURRENT_DATE, date_entree)
				END
			) AS nb
		FROM visites
		GROUP BY ref_bateau
	) tmp
)

[Anandamine]

Effectivement il manquait un L mais je l'avais remarqué...
En fait, je pensais que ma requête serait facile à réduire avec une utilisation pointue des alias mais j'y renonce.
Merci encore pour ton aide.

[Anandamine]

OOps sorry Phil, mais j'ai oublié de préciser que ta seconde requête me retourne bien le bateau qui a le plus de jours de présence, mais le nombre de jours est faux...

[CinePhil]

Euh... tu es sûr ?

Quel résultat as-tu ? Quel résultat attends-tu ?

[Anandamine]

Ben oui, avec ta requête j'obtiens 113 jours
et avec ma requête et ma calculette j'obtiens 133 jours.
Pourtant dans ta requête le calcul est bon semble-t-il(c'est le même à part ton currentdate qui est d'ailleurs plus approprié que mon now)

[CinePhil]

Euh... avec le jeu de données que tu as fourni, j'obtenais chez moi, à la date du 26 mai, 132 jours.

[Anandamine]

Bizarre.
Je viens encore de vérifier, et ça fait 113 pour toi et 133 pour moi.
J'ai mis la BD en attachement pour que tu puisses vérifier par toi-même...

[CinePhil]

C'est parce que ta colonne de date est NOT NULL donc mon IS NOT NULL ne peut pas fonctionner.
Voici la bonne requête, qui donne 134 jours aujourd'hui :

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
SELECT b.nom_bateau,
	SUM(
		CASE 
			WHEN v.date_sortie <>'0000-00-00' THEN DATEDIFF(v.date_sortie, v.date_entree)
			ELSE DATEDIFF(CURRENT_DATE, v.date_entree)
		END
	) AS nb_jours_bateau
FROM bateaux b
INNER JOIN visites v ON v.ref_bateau = b.id_bateau
GROUP BY b.nom_bateau
HAVING nb_jours_bateau = 
(
	SELECT MAX(nb)
	FROM
	(
		SELECT ref_bateau,
			SUM(
				CASE 
					WHEN date_sortie <>'0000-00-00' THEN DATEDIFF(date_sortie, date_entree)
					ELSE DATEDIFF(CURRENT_DATE, date_entree)
				END
			) AS nb
		FROM visites
		GROUP BY ref_bateau
	) tmp
)

[Anandamine]

C'est parce que ta colonne de date est NOT NULL donc mon IS NOT NULL ne peut pas fonctionner.

Ok, tout s'explique...Enfin pas tout à fait puisque ma requête fonctionne avec IS NOT NULL or elle devrait foirer

Ta requête est superbe Phil, je la mets au frais pour les grandes occasions.

Merci encore.