ma requete me semblait logique. pourtant

[plack]

bonjour,
je viens de réaliser un petit projet en utilisant une base de données mysql.
j'ai regroupé mes résultats dans une table (test)
alors cette table regroupe des identifiants des dates et des états de test
un identifiant pourrait avoir plusieurs états selon la date
donc un enregistrement peut avoir le meme identifiant qu'un autre et meme le meme état mais la date elle diffère.
bref, je voudrais connaitre l'état de chaque identifiant à la première date de son test et son état a la date de son dernier test.
voici ma requete sql :

Code :

1
2
3
4
SELECT ident,min(datte),max(datte),etat 
FROM test 
GROUP BY ident
HAVING(count(ident)>1); // le HAVING c'est pour ne prendre que les ident qui ont refait le test.

cette requete me donne un enregistrement par ident avec biensur la date sup la date min mais malheureusement un seul etat (etat qui correspond au test de la date min)

je ne trouve vraiment pas comment accéder a 2 résultats par identifiant l'un pour la date du premier test et l'autre pour la date du dernier.

merci d'avance pour vos suggestions

[CinePhil]

Avec une requête UNION peut-être :

Code :

1
2
3
4
5
6
7
8
9
SELECT ident, min(datte) AS DateMini, etat AS etatDateMini, NULL AS DateMaxi, NULL AS etatDateMaxi
FROM test
GROUP BY ident
HAVING COUNT(ident) > 1
UNION
SELECT ident, NULL AS DateMini, NULL AS etatDateMini, MAX(datte) AS DateMaxi, etat AS etatDateMaxi
FROM test
GROUP BY ident
HAVING COUNT(ident) > 1

Mais ça donnera deux lignes par ident.

[plack]

bonsoir,
merci pour votre soutien
je viens de tester votre requête sql. en effet elle donne deux lignes par ident en plus les lignes sont décalées et surtout les champs(NULL) qui prennent la place de ce que apres (union 2 eme affichage) sera la date max et l'etat en cette date.
je voulais reprendre le tout en effectuant un peu de gymnastique sur la requete:

Code :

1
2
3
4
5
6
 
SELECT ident, min(datte) AS DateMini, etat AS etatDateMini, 
  MAX(datte) AS DateMaxi, etat AS etatDateMaxi
FROM test
GROUP BY ident
HAVING COUNT(ident) > 1;

mais malheureusement j'ai pas eu le resultat souhaité vu que les "etat" sont les memes toujours alors que ce n'est pas le cas. on dirait qu'il ne prend en consideration que l'etat du premier test et c'est donc ce qu'il affiche deux fois

[CinePhil]

Alors utilisons plutôt deux sous-requêtes avec une jointure entre elles :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT t1.ident, t1.DateMini, t1.EtatMini, t2.DateMaxi, t2.EtatMaxi
FROM (
  SELECT ident, MIN(datte) AS DateMini, etat AS EtatMini
  FROM test
  GROUP BY ident
  HAVING COUNT(*) > 1
) AS t1
INNER JOIN (
  SELECT ident, MAX(datte) AS DateMaxi, etat AS EtatMaxi
  FROM test
  GROUP BY ident
  HAVING COUNT(*) > 1
) AS t2 ON t1.ident = t2.ident
ORDER BY t1.ident

[plack]

rebonsoir,
encore merci pour votre précieuse aide (c'est mon premier post au début je voulais finir mon projet sans avoir recours aux forums developpez.com )
je viens de tester la nouvelle requete j'ai presque le meme resultat qu'avec la mienne,
en effet dans le tableau affiché les deux etat affichés sont identiques et correspondent après vérification à l'état lors du test où la date est égale à min(datte)...
je ne trouve vraiment plus d'explications pourtant ça me semblais assez logique d'afficher apres distinction des deux etats suivant la date ensuite en joignant les deux et ça marche pas, le problème persiste vraiment je suis bloqué là

[CinePhil]

En fait c'est logique ! (je fatigue aujourd'hui moi !)
Comme le GROUP BY est fait uniquement sur ident, il est bien capable de trouver le MIN(datte) et le MAX(datte) mais il prend le premier etat qu'il trouve donc il donne deux fois le même.

C'est donc un peu plus compliqué. On va décomposer, comme ça j'explique en même temps.
La requête ci-dessous donne le MIN(datte) et le MAX(datte) pour chaque ident :

Code :

1
2
3
4
SELECT ident, MIN(datte) AS DateMini, MAX(datte) AS DateMaxi
FROM test
GROUP BY ident
HAVING COUNT(*) > 1

Utilisons la requête précédente dans le FROM et joignons là deux fois à la table test pour obtenir les deux états différents :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT tmp.ident, tmp.DateMini, t1.Etat AS EtatMini, tmp.DateMaxi, t2.etat AS EtatMaxi
FROM (
  SELECT ident, MIN(datte) AS DateMini, MAX(datte) AS DateMaxi
  FROM test
  GROUP BY ident
  HAVING COUNT(*) > 1
) tmp
INNER JOIN test t1 ON t1.ident = tmp.ident AND t1.datte = tmp.DateMini
INNER JOIN test t2 ON t2.ident = tmp.ident AND t2.datte = tmp.DateMaxi
ORDER BY tmp.ident

J'ai testé chez moi cette solution avec une table bidon et quelques lignes et ça marche.
A voir si c'est parfaitement adapté à votre cas particulier.

[plack]

re,
enfin ça a marché
merci pour toute votre aide depuis cet après-midi
j'ai repris votre requete avec quelques modification puisque je voulais mettre la cerise sur le gateau et clore une fois pour le problème en affichant dans la dernière colonne le nombre de fois où on a testé l'état d'ident
la requete après modification est donc :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT tmp.ident, tmp.DateMini, t1.Etat AS EtatMini, tmp.DateMaxi, t2.etat AS EtatMaxi,count(tmp.ident)
FROM (
  SELECT ident, MIN(datte) AS DateMini, MAX(datte) AS DateMaxi
  FROM test WHERE(room=1) //ici room correspond a la chambre ou s'effectue le test
  GROUP BY ident
  HAVING COUNT(*) > 1 and min(datte)<max(datte)
) tmp
INNER JOIN test t1 ON t1.ident = tmp.ident AND t1.datte = tmp.DateMini
INNER JOIN test t2 ON t2.ident = tmp.ident AND t2.datte = tmp.DateMaxi
group by tmp.ident

ça m'affiche un resultat bizarre sur la dernière colonne(des fois je trouve meme 1 alors que l'ident a effectué plusieurs test ou encore des fois je trouve le nombre de test total effectué dans les deux room alors que je me suis restreint à la première)

[CinePhil]

Envoyé par plack

en affichant dans la dernière colonne le nombre de fois où on a testé l'état d'ident

Euh... moi pas trop comprendre là...

la requete après modification est donc :

Code :

ça m'affiche un resultat bizarre sur la dernière colonne(des fois je trouve meme 1 alors que l'ident a effectué plusieurs test ou encore des fois je trouve le nombre de test total effectué dans les deux room alors que je me suis restreint à la première

Etant donné qu'il n'y a qu'une ligne par ident dans la requête que j'ai donnée, y ajouter un GROUP BY ident ne sert à rien !
Et le COUNT(tmp.ident) dans un GROUP BY tmp.ident donnera forcément 1.

Expliquez-moi mieux votre nouveau besoin, avec un tableau de données par exemple.


PS : comme vous n'utilisez pas les balises code et que vous avez ajouté le WHERE dans la sous-requête tmp à la suite du FROM, je ne l'avais pas vu.
Même en le voyant, j'ai dû mal à saisir votre nouveau besoin.

[skuatamad]

Salut,

Je pense que déplacer le count dans la sous-requête doit faire ce que tu veux :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT tmp.ident, tmp.DateMini, t1.Etat AS EtatMini, tmp.DateMaxi, t2.etat AS EtatMaxi, tmp.nb_test
FROM (
  SELECT ident, MIN(datte) AS DateMini, MAX(datte) AS DateMaxi, count(*) AS nb_test
  FROM test 
  WHERE room=1
  GROUP BY ident
  HAVING COUNT(*) > 1 AND min(datte)!=max(datte)
) tmp
INNER JOIN test t1 ON t1.ident = tmp.ident AND t1.datte = tmp.DateMini
INNER JOIN test t2 ON t2.ident = tmp.ident AND t2.datte = tmp.DateMaxi
ORDER BY tmp.ident

Sinon, méfie toi des faux semblants en sql, car ton affirmation est totalement fausse :

Envoyé par plack

cette requete me donne un enregistrement par ident avec biensur la date sup la date min mais malheureusement un seul etat (etat qui correspond au test de la date min)

La doc dit au sujet des champs cachés dans une clause GROUP BY :

En SQL standard, vous devriez ajouter la colonne customer.name à la clause GROUP BY. Avec MySQL, ce nom est redondant si vous n'utilisez pas le mode ANSI.
N'utilisez pas cette fonctionnalité si les colonnes que vous omettez dans la clause GROUP BY ne sont pas unique dans le groupe!! Vous auriez des résultats inattendus!

Ce qu'un simple test permet de vérifier :

Code :

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
 
mysql> CREATE TABLE test2 (id_t int AUTO_INCREMENT PRIMARY KEY,ident int,etat varchar(15), datte datetime);
Query OK, 0 rows affected (0.00 sec)
 
mysql> DROP procedure IF EXISTS test_group;
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter !!
mysql> CREATE procedure test_group()
    -> begin
    -> declare i int UNSIGNED DEFAULT 0;
    -> while i < 10000 do
    -> INSERT INTO test2 (ident,etat,datte)
    -> SELECT round(rand()*1000),concat('XXXX',round(rand()*20)), DATE_ADD(CURDATE(),INTERVAL round(rand()*100000) SECOND);
    -> SET i = i + 1;
    -> end while;
    -> end !!
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
mysql> call test_group();
Query OK, 1 row affected (0.84 sec)
 
mysql> 
mysql> CREATE TABLE test_min_id AS 
    -> SELECT ident, etat, min(datte) AS min_date
    -> FROM test2
    -> GROUP BY ident;
Query OK, 1001 rows affected (0.40 sec)
Records: 1001  Duplicates: 0  Warnings: 0
 
mysql> SELECT ident,count(DISTINCT etat) AS nb
    -> FROM test2
    -> GROUP BY ident
    -> ORDER BY nb DESC LIMIT 5;
+-------+----+
| ident | nb |
+-------+----+
|   605 | 15 | 
|   798 | 15 | 
|   668 | 14 | 
|   478 | 14 | 
|    27 | 14 | 
+-------+----+
5 rows IN SET (0.02 sec)
 
mysql> SELECT * FROM test2 WHERE ident=605 ORDER BY datte;
+------+-------+--------+---------------------+
| id_t | ident | etat   | datte               |
+------+-------+--------+---------------------+
| 2401 |   605 | XXXX12 | 2008-09-11 03:45:28 | 
| 9457 |   605 | XXXX12 | 2008-09-11 06:10:58 | 
| 9668 |   605 | XXXX8  | 2008-09-11 06:18:35 | 
| 5472 |   605 | XXXX16 | 2008-09-11 07:41:47 | 
| 2286 |   605 | XXXX9  | 2008-09-11 10:11:08 | 
| 5414 |   605 | XXXX13 | 2008-09-11 10:31:52 | 
| 1689 |   605 | XXXX17 | 2008-09-11 12:22:00 | 
|  429 |   605 | XXXX1  | 2008-09-11 13:59:50 | 
|  408 |   605 | XXXX6  | 2008-09-11 15:35:57 | 
| 6087 |   605 | XXXX14 | 2008-09-11 16:25:43 | 
| 8983 |   605 | XXXX2  | 2008-09-11 17:09:58 | 
| 2730 |   605 | XXXX10 | 2008-09-11 21:29:16 | 
| 4970 |   605 | XXXX19 | 2008-09-11 22:43:04 | 
| 4958 |   605 | XXXX3  | 2008-09-12 00:03:24 | 
| 2672 |   605 | XXXX11 | 2008-09-12 00:08:35 | 
| 9425 |   605 | XXXX15 | 2008-09-12 01:52:52 | 
+------+-------+--------+---------------------+
16 rows IN SET (0.01 sec)
 
mysql> SELECT ident, etat, min(datte)
    -> FROM test2
    -> WHERE ident=605
    -> GROUP BY ident;
+-------+-------+---------------------+
| ident | etat  | min(datte)          |
+-------+-------+---------------------+
|   605 | XXXX6 | 2008-09-11 03:45:28 | 
+-------+-------+---------------------+
1 row IN SET (0.00 sec)
 
mysql> SELECT count(*)
    -> FROM test_min_id t2 
    -> WHERE NOT EXISTS (SELECT 1 FROM test2 t1 WHERE t1.ident=t2.ident AND t1.etat=t2.etat AND t1.id_t = (SELECT min(t3.id_t) FROM test2 t3 WHERE t3.ident=t2.ident));
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row IN SET (51.41 sec)

Donc il semble (mais je ne suis pas sûr ) que les valeurs de etat soient celles des premières insertions en base par ident (et pas complètement des "résultats inattendus" ).
En tout cas, certainement des valeurs correspondant souvent au min(datte) dans ton cas, surtout en période de test

PS : Je précise que sélectionner des champs cachés dans le GROUP BY, est une très mauvaise pratique qui ne fonctionne que sous mysql, et qui est bien plus générateur d'erreurs qu'autres choses...
Bien sûr, toutes précisions sur les "résultats inattendus" m'interessent

[plack]

bonjour,
merci a vous tous c'est bon cette fois-ci, le count génère les nombres voulus (j'ai testé pour plusieurs ident).
remarque : en faisant "ORDER BY" j'ai eu plusieurs lignes par ident c'est ce qui explique le fait que je l'ai substitué par "GROUP BY".
encore merci pour votre aide et la prochaine fois je serais bien meilleur que cette fois car je me forme par des tutoriels au fur et a mesure

[skuatamad]

Envoyé par plack

remarque : en faisant "ORDER BY" j'ai eu plusieurs lignes par ident c'est ce qui explique le fait que je l'ai substitué par "GROUP BY".

Non surtout pas !
Si la requête te renvoie plusieurs lignes, c'est parce il y a des dates identiques, peut être est ce un champ date.
Il faut donc faire autrement, mais n'utilise pas un GROUP BY avec des champs cachés !

[plack]

bonsoir,
en fait j'avais fait le test en réseau de l'application et ça a été acceptée par mes encadreurs avec le group by
cela dit pour ma propre culture j'en tiendrais compte dans mes projets a venir
merci pour les conseils

[skuatamad]

Envoyé par plack

bonsoir,
en fait j'avais fait le test en réseau de l'application et ça a été acceptée par mes encadreurs avec le group by

Rassure toi, dans ton cas un GROUP BY tmp.ident en fin de requête, peut être envisagé car les colonnes tmp.DateMini, tmp.DateMaxi, tmp.nb_test sont constantes par ident donc pas de soucis.
En fait, tu laisses le choix à mysql de favoriser un t2.etat ou un t1.Etat plutôt qu'un autre mais parmis des résultats justes, donc ça passe même si tu ne maîtrises pas tout

Bonne continuation, et pense au