Supprimer un groupe sur la base d'un critère rempli par un membre du groupe [Résolu]

[Etienne812]

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 !

[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 sql :

 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 ?

[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

[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 !!

[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');

[salim11]

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.

[lola06]

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 :

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.

[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

[salim11]

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.

[Waldar]

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.