Supprimer un groupe sur la base d'un critère rempli par un membre du groupe

Version imprimable

10/02/2012, 15h58
Etienne812

Supprimer un groupe sur la base d'un critère rempli par un membre du groupe
Bonjour,

Je cherche à écrire une procédure qui me permette de supprimer d'une table tout un groupe ayant un même nom (ou autre critère) pour lequel une des lignes du groupe seulement remplit un certain critère :

J’ai la table suivante 'Table_de_depart'
Code:

1 2 3 4 5 6 7 NOM NOTE DATE RANG (de la note) Alpha AA 2006 3 Alpha NR 1998 27 Alpha A+ 2004 5 Beta NR 2004 27 Beta BB 2006 12 Beta BB+ 2001 11
Je cherche à obtenir tous les noms qui ont une note entre 2001 et 2006, mais qui n’ont pas de NR entre ces deux dates, c'est-à-dire les résultats suivants :
Code:

1 2 3 NOM NOTE DATE RANG (de la note) Alpha AA 2006 3 Alpha A+ 2004 5
Toutes les entrées ‘Beta’ ont été supprimées, car Beta contient au moins une note NR entre les dates 2001 et 2006

Merci pour votre aide !
NB : je débute en SQL, merci d’être très pédagogue et explicite dans vos réponses !
10/02/2012, 16h07
Etienne812
Voici mon idée, mais toute autre idée est la bienvenue :

Je crée une table_deleted où je supprime tous les résultats contenant ‘NR’ entre les 2 dates désirées :

Code:

DELETE from Table_de_depart where DATE =< 2006 and 2001 =< DATE and NOTE = ‘NR’ ;

Je devrais obtenir :
Code:

1 2 3 4 5 6 NOM NOTE DATE RANG (de la note) Alpha AA 2006 3 Alpha NR 1998 27 Alpha A+ 2004 5 Beta BB 2006 12 Beta BB+ 2001 11
J’appelle Table_deleted ce résultat : mais je ne sais pas faire ça ! j’ai posté un message à ce sujet : « Créer une table à partir des résultats d’un DELETE ? »

J’appelle Table_intermédiaire le résultat :

Code:

Table_de_depart MINUS Table_deleted

, c'est-à-dire :
Code:

1 2 NOM NOTE DATE RANG Beta NR 2004 27
Maintenant je supprime de la table_de_depart tous les NOM qui se retrouvent dans la table intermédiaire, je filtre aussi sur les dates (2001 =< DATE =< 2006) et j’obtiens le résultat :
Code:

1 2 3 NOM NOTE DATE RANG Alpha AA 2006 3 Alpha A+ 2004 5
Est-ce que cela vous semble correct comme démarche ? Y a-t-il plus simple ? Comment créer la table Table_deleted ?
10/02/2012, 16h11
mnitu

Pour l’instant essayez d’écrire la requête qui à partir de votre jeux d’essai trouve tous les enregistrements correspondantes à vos critères : une note entre 2001 et 2006, mais qui n’ont pas de NR entre ces deux dates
10/02/2012, 17h30
lola06
Je pense qu'il vaut mieux prendre le problème à l'envers.

- tu sélectionnes ta ligne avec NR et date > 2001 et < 2006
Code:

1 2 3 4 SELECT nom, note, date, rang FROM MaTable WHERE date BETWEEN 2001 AND 2006 AND note = 'NR'
- tu récupères juste le nom que tu supprime de ta sélection
Code:

1 2 3 4 5 6 7 SELECT * FROM MaTable WHERE nom NOT IN (SELECT DISTINCT nom FROM MaTable WHERE date BETWEEN 2001 AND 2006 AND note = 'NR') AND date BETWEEN 2001 AND 2006
Est-ce que cette requête vous donne le bon résultat ?

Au passage j'espère que 'date' n'est pas le vrai nom de ta colonne !!

Il manque encore un filtre dans la requête de lola06, et le distinct est inutile dans les sous-requêtes IN / NOT IN :

Code:

1
2
3
4
5
6
7
8
SELECT *
  FROM MaTable
 WHERE date BETWEEN 2001 AND 2006
   AND NOT <> 'NR'
   AND nom NOT IN (SELECT nom
                     FROM MaTable
                    WHERE date BETWEEN 2001 AND 2006
                      AND note = 'NR');

Bonjour,

Une autre solution sans scanner la table Matble deux fois.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH matable1 AS
     (SELECT nom, note, dt, rang,
             COUNT (CASE
                       WHEN note = 'NR'
                          THEN 1
                    END) OVER (PARTITION BY nom) exist
        FROM matable
       WHERE dt BETWEEN 2001 AND 2006)
SELECT nom, note, dt, rang, exist
  FROM matable1
 WHERE exist = 0

La démonstration

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
 
WITH matable AS
     (SELECT 'Alpha' nom, 'AA' note, 2006 dt, 3 rang
        FROM DUAL
      UNION ALL
      SELECT 'Alpha', 'NR', 1998, 27
        FROM DUAL
      UNION ALL
      SELECT 'Alpha', 'A+', 2004, 5
        FROM DUAL
      UNION ALL
      SELECT 'Beta', 'NR', 2004, 27
        FROM DUAL
      UNION ALL
      SELECT 'Beta', 'BB', 2006, 12
        FROM DUAL
      UNION ALL
      SELECT 'Beta', 'BB+', 2001, 11
        FROM DUAL),
     matable1 AS
     (SELECT nom, note, dt, rang,
             COUNT (CASE
                       WHEN note = 'NR'
                          THEN 1
                    END) OVER (PARTITION BY nom) exist
        FROM matable
       WHERE dt BETWEEN 2001 AND 2006)
SELECT nom, note, dt, rang, exist
  FROM matable1
 WHERE exist = 0
 
 
NOM   NOT         DT       RANG      EXIST
----- --- ---------- ---------- ----------
Alpha A+        2004          5          0
Alpha AA        2006          3          0
 
 
2 rows selected.

Citation:
Envoyé par Waldar
Il manque encore un filtre dans la requête de lola06, et le distinct est inutile dans les sous-requêtes IN / NOT IN :
Code:

1 2 3 4 5 6 7 8 SELECT * FROM MaTable WHERE date BETWEEN 2001 AND 2006 AND NOT <> 'NR' AND nom NOT IN (SELECT nom FROM MaTable WHERE date BETWEEN 2001 AND 2006 AND note = 'NR');
Est ce que le NOTE <> 'NR' est vraiment utile ? Car ma sous-requête permet de ne pas sélectionner un groupe dès qu'un NR est dedans. Et donc il n'est pas possible qu'il réapparaisse par la suite.

13/02/2012, 10h36
Etienne812

Merci beaucoup à vous tous, Lola06, Waldar et Salim11 !!
Je me doutais bien que je prenais le problème à l'envers.
J'utilise la requête de Waldar cela fonctionne très bien. La requête de Lola06 aussi fonctionne et retourne les mêmes résultats. Ce que fait Salim me paraît plus compliqué et je ne comprends pas vraiment.

Merci infiniment encore !

PS : il faut bien sûr lire NOTE au lieu de NOT dans la requête de Waldar, pour le nom de colonne, et @Lola06 : oui ma colonne ne s'appelle évidemment pas DATE, j'essayais juste de donner des noms très explicites :)
13/02/2012, 20h50
salim11

Citation:

Envoyé par Etienne812

Ce que fait Salim me paraît plus compliqué et je ne comprends pas vraiment.

Ma requête est vraiment simple:

1-Je comptabilise dans ma première requête le nombre de fois qu'apparait note = 'NR' par nom avec la dt entre 2001 et 2006.

2- si ce nombre (exist)=0 alors j'obtiens tous les noms qui ont une note entre 2001 et 2006, mais qui n’ont pas de NR

Si tu as des questions n'hésite pas.

Cordialement Salim.
13/02/2012, 22h56
Waldar

Citation:

Envoyé par lola06

Est ce que le NOTE <> 'NR' est vraiment utile ? Car ma sous-requête permet de ne pas sélectionner un groupe dès qu'un NR est dedans. Et donc il n'est pas possible qu'il réapparaisse par la suite.

Entre le jeu de départ et le résultat souhaité, tout le groupe beta est exclu (par le not in), mais parmi les informations du groupe alpha la note NR (deuxième ligne) disparaît aussi.
Mais l'essentiel de la requête c'est bien le NOT IN.

La solution de salim11 est très bien aussi, probablement de plus en plus performante avec une augmentation de volumétrie.