tri de donnée mysql

**BIGnotor** · 05/11/2015, 12h37

Bonjour à tous,

J'ai un soucis quand à réaliser une requète MySQL pour faire un tri dans mes données:

Explication: je dois afficher tous les candidats qui ont eu une note en dessous de 14 pour les épreuves '001','002','003' et qui ont Aussi eu une note inférieure à 7 pour les épreuves '007','008','301','302','303','304'.

J'ai commencé à tester des solutions pour une condition mais les résultats ne sont pas la..

Description des Tables

cnd_epr : table des épreuves
num_can : numéro d candidat
cod_epr : le code de l'épreuve
not_epr : la note obtenue à l'épreuve

quelque test pour vous aider a comprendre mon problème:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT candidat,
notes001,
notes002
 
 FROM (
SELECT DISTINCT (cnd_epr.num_can) AS candidat,
       CASE cnd_epr.cod_epr WHEN  '001' 
           THEN cnd_epr.not_epr END  AS note001,
       CASE cnd_epr.cod_epr WHEN  '002' 
           THEN cnd_epr.not_epr END  AS note002
 
      FROM cnd_epr 
      ) AS triage

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Select DISTINCT cnd_epr.num_can
FROM
(
SELECT DISTINCT (cnd_epr.num_can) AS candidat,
       CASE  WHEN  cnd_epr.cod_epr IN ('001', '002', '003')  
           THEN cnd_epr.not_epr END  AS notegroupa
 
      FROM cnd_epr 
 ) AS s1
  WHERE cnd_epr.exa_men = 'dnbo' 
	AND cnd_epr.exa_typ = 'dnb006' 
	AND cnd_epr.exa_ann = '2015' 
	AND cnd_epr.exa_ses = '06'
    AND notegroupa < 14

Y'a t il quelqu'un qui pourrait m'éclaircir, Merci d'avance,
Cordialement,
BIGnotor.

**escartefigue** · 05/11/2015, 14h02

Bonjour,

Essayez ceci :

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
SELECT num_can,                                                   
       cod_epr,                                                   
       not_epr                                                    
FROM MA_TABLE MAIN                                                  
WHERE MAIN.cod_epr IN ( 001 , 002 , 003 )                         
  AND MAIN.not_epr < 14                                           
  AND EXISTS                                                    
    (SELECT 1                                                   
     FROM MA_TABLE SUBQ                                             
     WHERE SUBQ.num_can = MAIN.num_can                              
       AND SUBQ.cod_epr IN ( 007 , 008 , 301 , 302 , 303 , 304 )  
       AND SUBQ.not_epr < 07)
UNION                                                      
SELECT num_can,                                              
       cod_epr,                                              
       not_epr                                               
FROM MA_TABLE MAIN                                             
WHERE MAIN.cod_epr IN ( 007 , 008 , 301 , 302 , 303 , 304 )  
  AND MAIN.not_epr < 07                                      
  AND EXISTS                                               
    (SELECT 1                                              
     FROM MA_TABLE SUBQ                                        
     WHERE SUBQ.num_can = MAIN.num_can                         
       AND SUBQ.cod_epr IN ( 001 , 002 , 003 )               
       AND SUBQ.not_epr < 14)

**BIGnotor** · 05/11/2015, 17h03

Bonjour, merci d'avoir répondu,

à quoi correspond ma table subq ? tout est dans la même tables, juste des colonnes différentes..

De mon coté j'en suis arrivé à ceci en final.. ca met du temps a s'éxecuter mais ca me sors quelque chose d'assez cohérant, j'approfondirai demain

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
SELECT DISTINCT nmn_can, nom_can, pre_can, cnd.num_can 
 
FROM cnd_epr, 
(SELECT cnd_epr.num_can, 
	CASE WHEN cnd_epr.cod_epr = '001' THEN cnd_epr.not_epr < 14 
		 WHEN cnd_epr.cod_epr = '002' THEN cnd_epr.not_epr < 14 
         WHEN cnd_epr.cod_epr = '003' THEN cnd_epr.not_epr < 14 
         WHEN cnd_epr.cod_epr = '007' THEN cnd_epr.not_epr < 7 
         WHEN cnd_epr.cod_epr = '008' THEN cnd_epr.not_epr < 7 
         WHEN cnd_epr.cod_epr = '301' THEN cnd_epr.not_epr < 7 
         WHEN cnd_epr.cod_epr = '302' THEN cnd_epr.not_epr < 7 
         WHEN cnd_epr.cod_epr = '303' THEN cnd_epr.not_epr < 7 
         WHEN cnd_epr.cod_epr = '304' THEN cnd_epr.not_epr < 7 END 
    FROM cnd_epr) as test001 
WHERE   cnd.exa_men = 'dnbo' 
	AND cnd.exa_typ = 'dnb006' 
	AND cnd.exa_ann = '2015' 
	AND cnd.exa_ses = '06' 
	AND cnd.cod_cat = '520' 
    AND cnd_epr.num_can IN 
    (SELECT cnd_epr.num_can 
    FROM cnd_epr
    WHERE   cnd_grp.exa_men = 'dnbo' 
		AND cnd_grp.exa_typ = 'dnb006' 
		AND cnd_grp.exa_ann = '2015' 
		AND cnd_grp.exa_ses = '06' 
		AND cnd_grp.moy_cal < 9.59  
		AND cnd_grp.moy_cal > 9 )
 
	AND cnd.num_can = cnd_grp.num_can 
    AND cnd_grp.num_can = cnd_epr.num_can

bye

**escartefigue** · 06/11/2015, 09h06

SUBQ est juste un alias (un surnom) qui permet de faire une sous-requête (subquery) sur la même table
de même MAIN est un alias pour désigner cette table dans la requête principale
On ne peut pas utiliser plusieurs fois une même table dans une requête sans utiliser d'alias

J'ai testé cette requête avant de vous la proposer, vous pouvez donc l'utiliser telle qu'elle

EDIT : pensez juste à modifier les prédicats in, dans mon jeu d'essai, j'ai créé la colonne épreuve en numérique, de votre coté, il faut donc ajouter les simples quotes

**BIGnotor** · 09/11/2015, 09h17

j'ai passé la fin de la semaine a essayer d'améliorer cette requete et je n'obtient pas de résultat... je suis en galère encore.. si vous pouviez m'aider encore une fois Merci d'avance

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
41
42
43
SELECT DISTINCT nmn_can, nom_can, pre_can, c.num_can 
 
FROM cnd_epr, cnd c,
 
	(SELECT num_can, cod_epr, not_epr 
	FROM cnd_epr a   
	WHERE a.cod_epr IN ( '001' , '002' , '003' )  
	    AND a.not_epr < 14 
	    AND EXISTS 
		  (SELECT 1  
		   FROM cnd_epr b  
		   WHERE b.num_can = a.num_can 
		       AND b.cod_epr IN ( '007' , '008' , '301' , '302' , '303' , '304' )  
		       AND b.not_epr < 07) 
       UNION  
	(SELECT num_can, cod_epr, not_epr 
	FROM cnd_epr a 
	WHERE a.cod_epr IN ( '007' , '008' , '301' , '302' , '303' , '304' ) 
	    AND a.not_epr < 07 
	    AND EXISTS  
		  (SELECT 1 
		   FROM cnd_epr b 
		   WHERE b.num_can = a.num_can 
		       AND b.cod_epr IN ( '001' , '002' , '003' ) 
		       AND b.not_epr < 14)))RQ
 
WHERE cnd.exa_men = 'dnbo' 
    AND cnd.exa_typ = 'dnb006' 
    AND cnd.exa_ann = '2015' 
    AND cnd.exa_ses = '06' 
    AND cnd.cod_cat = '520' 
    AND EXISTS  
    (SELECT 1 
    FROM cnd_epr c
    WHERE   c.num_can = c.num_can
        AND c.exa_men = 'dnbo' 
	AND c.exa_typ = 'dnb006' 
	AND c.exa_ann = '2015' 
	AND c.exa_ses = '06' 
	AND c.moy_cal < 9.59  
	AND c.moy_cal > 9) 
AND cnd.num_can = cnd_epr.num_can 
AND cnd_epr.num_can = cnd_grp.num_can

**escartefigue** · 09/11/2015, 10h29

Bonjour,

Je vous ai envoyé la requête qui correspond exactement à votre expression de besoin initial, ici visiblement c'est un nouveau besoin ou une grosse évolution

Le plus simple est que vous reformuliez votre nouveau besoin fonctionnel, afin que les contributeurs puissent vous proposer une nouvelle solution

**BIGnotor** · 09/11/2015, 11h22

La requête dont nous avons besoin a pour but d'identifier les candidats qui correspondent à tous les critères suivants :
- candidats individuels (code 520)
- candidats ayant une moyenne générale comprise entre 9 et 9.59
- candidats ayant 2 notes maximum comprises entre 0 et 6.99 :
-- ces 2 notes sont des notes sur 20, donc besoin de convertir les notes sur 40 (français '001', maths '002' et histoire-géo '003') pour que cette condition de note inférieure à 7 soit respectée. (reste des notes: '007' '008' '301' '302' '303' '304')

J'ai besoin d'avoir uniquement les noms et prénoms de ces candidats.

Il y a des conditions obligatoires:
exa_men = 'dnbo'
exa_typ = 'dnb006'
exa_ann = '2015'
exa_ses = '06'

TABLE CND = Informations sur le candidat (nom, prénom, num_can, cod_cat (520)...)
TABLE CND_EPR = Informations sur l'épreuve (cod_epr ('001' ... '304') not_epr...)
TABLE CND_GRP = Informations sur la moyenne (moy_cal < 9...)

Et donc avec mes recherches et mes test, j'ai abouti a cela, mais cette requête ne marche pas..

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
41
42
43
SELECT DISTINCT nmn_can, nom_can, pre_can, c.num_can 
 
FROM cnd_epr, cnd c,
 
	(SELECT num_can, cod_epr, not_epr 
	FROM cnd_epr a   
	WHERE a.cod_epr IN ( '001' , '002' , '003' )  
	  AND a.not_epr < 14 
	  AND EXISTS 
		(SELECT 1  
		 FROM cnd_epr b  
		 WHERE b.num_can = a.num_can 
		   AND b.cod_epr IN ( '007' , '008' , '301' , '302' , '303' , '304' )  
		   AND b.not_epr < 07) 
       UNION  
	(SELECT num_can, cod_epr, not_epr 
	FROM cnd_epr a 
	WHERE a.cod_epr IN ( '007' , '008' , '301' , '302' , '303' , '304' ) 
	  AND a.not_epr < 07 
	  AND EXISTS  
		(SELECT 1 
		 FROM cnd_epr b 
		 WHERE b.num_can = a.num_can 
		   AND b.cod_epr IN ( '001' , '002' , '003' ) 
		   AND b.not_epr < 14)))RQ
 
WHERE cnd.exa_men = 'dnbo' 
  AND cnd.exa_typ = 'dnb006' 
  AND cnd.exa_ann = '2015' 
  AND cnd.exa_ses = '06' 
  AND cnd.cod_cat = '520' 
  AND EXISTS  
    (SELECT 1 
    FROM cnd_epr c
    WHERE   c.num_can = c.num_can
        AND c.exa_men = 'dnbo' 
		AND c.exa_typ = 'dnb006' 
		AND c.exa_ann = '2015' 
		AND c.exa_ses = '06' 
		AND c.moy_cal < 9.59  
		AND c.moy_cal > 9) 
AND cnd.num_can = cnd_epr.num_can 
AND cnd_epr.num_can = cnd_grp.num_can

Cordialement

**escartefigue** · 09/11/2015, 11h52

La requete que je vous avais proposée pour votre 1er besoin n'est donc plus utilisable :

Cette requete extrait les personnes ayant eu au moins 1 note < 7 pour les disciplines notées sur 20 ET au moins une note < 14 pour celles notées sur 40
Or, dans votre nouveau besoin, une personne ayant au maximum 2 notes < 7/20 mais aucune note < 14/40 doit être extraite (et inversement)

Afin de vous affranchir des problèmes de notation tantôt sur 20 tantôt sur 40, calculez la moyenne sur 20 pour toutes les matières avec un CASE when, puis extrayez les individus ayant maxi 2 notes < 7/20

Vu que MySQL ne sait pas gérer les CTE, ca va vous faire pas mal de requetes imbriquées

**BIGnotor** · 13/11/2015, 10h10

Je suis arrivé à ceci, ce qui a pas mal changé depuis le premier commentaire sur cette discussion mais je n'arrive pas a avoir de résultats concrès, je n'ai pas la "Mentalité MySQL".... :/

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
SELECT DISTINCT c.num_can, c.pre_can, c.nmn_can  
FROM  cnd c, cnd_epr ce, 
	(SELECT DISTINCT c.num_can, 
	 CASE WHEN ce.cod_epr = '001' THEN ce.not_epr /2 
		  WHEN ce.cod_epr = '002' THEN ce.not_epr /2 
          WHEN ce.cod_epr = '003' THEN ce.not_epr /2 END 
      FROM cnd_epr ce, cnd c  
     WHERE ce.exa_men = 'dnbo' 
	   AND ce.exa_typ = 'dnb006' 
	   AND ce.exa_ann = '2015' 
	   AND ce.exa_ses = '06' 
	   AND ce.cod_epr IN ('001','002','003','007','008','301','302','303','304') 
	 HAVING COUNT(ce.not_epr) <= 2  ) AS notdiv2 , 
 
	(SELECT DISTINCT c.num_can 
	   FROM cnd c, cnd_grp cg  
	   WHERE c.exa_men = 'dnbo' 
	     AND c.exa_typ = 'dnb006' 
	     AND c.exa_ann = '2015' 
	     AND c.exa_ses = '06' 
		 AND c.cod_cat = '520' 
         AND cg.moy_cal <= 9.59 
         AND cg.moy_cal >= 9 
         AND c.num_can = cg.num_can ) AS moygen      
WHERE c.exa_men = 'dnbo' 
  AND c.exa_typ = 'dnb006' 
  AND c.exa_ann = '2015' 
  AND c.exa_ses = '06' 
  AND ce.exa_men = 'dnbo' 
  AND ce.exa_typ = 'dnb006' 
  AND ce.exa_ann = '2015' 
  AND ce.exa_ses = '06' 
  AND ce.num_can = c.num_can  
ORDER BY c.num_can ASC

Si vous apercevez une grosse erreur, dites le moi SVP,
Cordialement.

**escartefigue** · 13/11/2015, 11h11

Encapsulez la requête ci-dessous pour faire une jointure avec votre table des candidats en filtrant ceux qui ont max 2 notes et le tour est joué

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT num_can, COUNT(*)                                              
FROM                                                                  
   (SELECT EPR.num_can                                                
          ,EPR.cod_epr                                                
          ,CASE WHEN EPR.cod_epr BETWEEN '001' AND '003'              
                THEN EPR.not_epr / 2                                  
           ELSE                                                       
                EPR.not_epr                                           
           END AS N20                                                 
    FROM MATABLE AS EPR                                               
    WHERE EPR.cod_epr IN (001, 002, 003, 007, 008, 301, 302, 303, 304)
    ) AS TMP                                                          
    WHERE TMP.N20 < 7                                                 
    GROUP BY num_can

**escartefigue** · 13/11/2015, 11h28

Ce qui donne ceci :

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
SELECT CAN.num_can, CAN.nom, CAN.prenom, COUNT(*)     
FROM                                                                  
   (SELECT EPR.num_can                                                
          ,EPR.cod_epr                                                
          ,CASE WHEN EPR.cod_epr BETWEEN '001' AND '003'              
                THEN EPR.not_epr / 2                                  
           ELSE                                                       
                EPR.not_epr                                           
           END AS N20                                                 
    FROM TAB_EPR AS EPR                                               
    WHERE EPR.cod_epr IN ('001', '002', '003', '007', '008', '301', '302', '303', '304')
    ) AS TMP            
   INNER JOIN TAB_CAN AS CAN              
      ON CAN.num_can = TMP.num_can              
   WHERE TMP.N20 < 7                        
     AND CAN.cod_cat = 520
   GROUP BY CAN.num_can, CAN.nom, CAN.prenom 
   HAVING COUNT(*) < 3

**BIGnotor** · 13/11/2015, 13h44

Ah merci escartefigue, en 5 secondes, j'ai un résultat qui sors, par contre, je ne sais pas encore s'il est bon vu qu'il manque un critère, je voulais savoir pour mettre le critère de moyenne générale qui est dans la table cnd_grp, je refait un INNER JOIN ou je peux l'insérer directement dans la suite de la requete mère?

je met le code:

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
SELECT c.num_can, c.nmn_can, c.pre_can     
FROM                                                                  
   (SELECT ce.num_can                                                
          ,ce.cod_epr                                                
          ,CASE WHEN ce.cod_epr BETWEEN '001' AND '003'              
                THEN ce.not_epr / 2                                  
           ELSE                                                       
                ce.not_epr                                           
           END AS notdiv2                                                  
    FROM cnd_epr AS ce                                               
    WHERE ce.cod_epr IN ('001', '002', '003', '007', '008', '301', '302', '303', '304') 
    AND ce.exa_men = 'dnbo' 
	AND ce.exa_typ = 'dnb006' 
	AND ce.exa_ann = '2015' 
	AND ce.exa_ses = '06' 
    ) AS TMP            
   INNER JOIN cnd AS c              
      ON c.num_can = TMP.num_can 
   INNER JOIN cnd_grp AS cg
	  ON cg.num_can = c.num_can 
   WHERE TMP.notdiv2 < 7                        
     AND c.cod_cat = 520
     AND c.exa_men = 'dnbo' 
	 AND c.exa_typ = 'dnb006' 
	 AND c.exa_ann = '2015' 
	 AND c.exa_ses = '06' 
     AND cg.moy_cal <= 9 
     AND cg.moy_cal >= 9.59 
   GROUP BY c.num_can, c.nmn_can, c.pre_can 
   HAVING COUNT(*) < 3
   ORDER BY c.nmn_can ASC

**escartefigue** · 13/11/2015, 14h44

Vous pouvez faire directement la jointure inner comme vous l'avez faite, par contre ce bout de code ne renverra jamais rien

Code :

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

1
2
3
 
     AND cg.moy_cal <= 9 
     AND cg.moy_cal >= 9.59

**BIGnotor** · 13/11/2015, 15h30

autant pour moi

(je perd la tête..)

tri de donnée mysql

MySQL

Discussions similaires

Partager

Partager