aide pour une requête complexe

**marsupio49** · 10/06/2007, 13h10

Bonjour à tous,
Je suis actuellement en stage informatique et on me demande de faire un certains nombres de requête afin d'afficher les résultats de transfert de fichier. Le problème est que pour un affichage, j'ai besoin de trois requètes et j'aimerais n'en faire que une, si bien sûr cela est possible. Je travail sur une base Oracle. Dont voici la table. (tout les nom son fictif, mais le principe reste le même)

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
44
 
-- 
-- Serveur: localhost
-- Généré le : Dimanche 10 Juin 2007 à 12:48
-- Version du serveur: 4.1.9
-- Version de PHP: 4.3.10
-- 
-- Base de données: `mabase`
-- 
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `matable`
-- 
 
CREATE TABLE `matable` (
  `idtransfert` int(11) NOT NULL auto_increment,
  `date` date NOT NULL default '0000-00-00',
  `statut` int(1) NOT NULL default '0',
  `nomfichier` varchar(60) NOT NULL default '',
  `typefichier` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`idtransfert`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=70 ;
 
-- 
-- Contenu de la table `matable`
-- 
 
INSERT INTO `matable` VALUES (15, '2007-11-05', 0, 'rapport_compta', '.txt');
INSERT INTO `matable` VALUES (5, '2007-06-01', 1, 'dossier_entr.', '.txt');
INSERT INTO `matable` VALUES (2, '2007-02-03', 1, 'mémoire', '.doc');
INSERT INTO `matable` VALUES (3, '2007-03-10', 1, 'logo1', '.gif');
INSERT INTO `matable` VALUES (6, '2007-06-01', 1, 'administration', '.doc');
INSERT INTO `matable` VALUES (7, '2007-06-02', 0, 'graphique', '.gif');
INSERT INTO `matable` VALUES (8, '2007-06-06', 0, 'rapport_stage', '.doc');
INSERT INTO `matable` VALUES (13, '2007-08-15', 0, 'reglement', '.txt');
INSERT INTO `matable` VALUES (12, '2007-06-24', 1, 'bannière', '.gif');
INSERT INTO `matable` VALUES (11, '2007-06-15', 1, 'note1', '.txt');
INSERT INTO `matable` VALUES (14, '2007-10-30', 0, 'note2', '.txt');
INSERT INTO `matable` VALUES (10, '2007-06-14', 1, 'rapport', '.doc');
INSERT INTO `matable` VALUES (9, '2007-06-07', 0, 'resultat_concour', '.txt');
INSERT INTO `matable` VALUES (4, '2007-05-21', 0, 'feuille_salaire', '.txt');
INSERT INTO `matable` VALUES (1, '2007-01-11', 0, 'facture', '.pdf');

Résultat d'un :

Code :

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

Select * from matable

Pour le statut, zéro signifie que le transfert est réussi (OK), le 1 signifie qu'il a échoué (KO).

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
 
idtransfert	date		statut	nomfichier		typefichier
15 		2007-11-05 	0 	rapport_compta 		.txt
5 		2007-06-01 	1 	dossier_entr. 		.txt
2 		2007-02-03 	1 	mémoire 		.doc
3 		2007-03-10 	1 	logo1 			.gif
6 		2007-06-01 	1 	administration 		.doc
7 		2007-06-02 	0 	graphique 		.gif
8 		2007-06-06 	0 	rapport_stage 		.doc
13 		2007-08-15 	0 	reglement 		.txt
12 		2007-06-24 	1 	bannière 		.gif
11 		2007-06-15 	1 	note1 			.txt
14 		2007-10-30 	0 	note2 			.txt
10 		2007-06-14 	1 	rapport 		.doc
9 		2007-06-07 	0 	resultat_concour 	.txt
4 		2007-05-21 	0 	feuille_salaire 	.txt
1 		2007-01-11 	0 	facture 		.pdf

Ce que je voudrais, c'est avoir un tableau ou nous verrions le dernier fichier de chaque typefichier, le nombre de fichier tranféré et le nombre de fichier KO.
exemple:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
-------------------------------------------------------------------------|
   date   |        nomfichier    | typefichier  | Nb_fichier |  nb_erreur|
-------------------------------------------------------------------------|
2007-11-05|    rapport_compta    |   .txt       |     7      |     2     |
-------------------------------------------------------------------------|
2007-06-14|    rapport           |   .doc       |     4      |     3     |
-------------------------------------------------------------------------|
2007-06-24|    bannière          |   .gif       |     3      |     2     |
-------------------------------------------------------------------------|
2007-01-11|    facture           |   .pdf       |     1      |     0     |
-------------------------------------------------------------------------|

Pour l'instant je n'ai réussi qu'en faisant 3 tableaux différents, en faisant trois requêtes :
Une pour les derniers fichier, une autre pour le nommbre de fichier par typefichier et une troisième pour le nombre de fichier KO.

J'aimerais savoir si il ne serait pas possible de pouvoir réduire le nombre de requête. Je ne sais pas du tout si cela est possible. Cela me parait compliquer

Voila ce que j'ai fait:

-requête pour le dernier fichier par type de fichier:

Code :

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

1
2
3
4
5
6
7
8
 
SELECT 	idtransfert, Statut, date, nomfichier, typefichier
FROM 	MaTable
WHERE 	idtransfert IN 
 
	(SELECT MAX(idtransfert)
	FROM MaTable
	GROUP BY (typefichier))

Résultat:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
-------------------------------------------------
   date   |        nomfichier    | typefichier  |
-------------------------------------------------
2007-11-05|    rapport_compta    |   .txt       |
-------------------------------------------------
2007-06-14|    rapport           |   .doc       |
-------------------------------------------------
2007-06-24|    bannière          |   .gif       |
-------------------------------------------------
2007-01-11|    facture           |   .pdf       |
-------------------------------------------------

-requête pour afficher la deuxième partie, le nombre de fichier par type de fichier:

Code :

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

1
2
3
4
 
SELECT COUNT(*) AS Nb_fichier, typefichier
FROM MaTable
GROUP BY (typefichier)

résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
---------------------------|
 typefichier  | Nb_fichier |
---------------------------|
   .txt       |     7      |    
---------------------------|
   .doc       |     4      |    
---------------------------|
   .gif       |     3      |     
---------------------------|
   .pdf       |     1      |     
---------------------------|

-requête pour afficher la troisième partie, le nombre de fichier KO par type de fichier:

Code :

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

1
2
3
4
5
 
SELECT COUNT(*) AS Nb_fichier, typefichier
FROM MaTable
WHERE statut=1
GROUP BY (typefichier)

résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
---------------------------|
 typefichier  | nb_erreur  |
---------------------------|
   .txt       |     2      |    
---------------------------|
   .doc       |     3      |    
---------------------------|
   .gif       |     2      |     
---------------------------|

!! De plus ici le type .pdf qui n'a acun fichier KO n'apparait pas, alors que je me doit de le faire apparaitre. :s

Donc voila je voulais savoir si vous pouviez m'aider à réduire le nombre de requête.En esperant vous avoir donné les informations qu'il fallait tout en étant clair.

Je vous remercie d'avance.

Cordialement,

**ylarvor** · 10/06/2007, 15h54

Code :

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

1
2
3
4
5
6
7
8
 
CREATE VIEW VueIntermediaire1 AS
SELECT idtransfert, Statut, date, nomfichier, typefichier FROM MaTableWHERE idtransfert IN (SELECT MAX(idtransfert) FROM MaTable GROUP BY (typefichier))
CREATE VIEW VueIntermediaire2 AS
SELECT COUNT(*) AS Nb_fichier, typefichierFROM MaTableGROUP BY (typefichier)
CREATE VIEW VueIntermediaire3 AS
SELECT COUNT(*) AS Nb_Erreur, typefichierFROM MaTableWHERE statut=1GROUP BY (typefichier)
CREATE VIEW VueFinale AS SELECT 1.idtransfert, 1.Statut, 1.date, 1.nomfichier, 1.typefichier, 2.Nb_fichier, 3.Nb_Erreur FROM VueIntermediaire1 As 1 RIGHT OUTER JOIN VueIntermediaire2 As 2 ON 1.typeFichier = 2.typeFichier RIGHT OUTER JOIN VueIntermediaire3 As 3 ON 2.typeFichier = 3.typeFichier

Pour ton problème de NULL, il suffit de faire un RIGHT OUTER JOIN au lieu d'un JOIN.

**remi4444** · 11/06/2007, 11h41

Remarques
- Attention tu es en syntaxe MySql, en oracle on n'encadre pas les noms de champs par des cotes mais par des guillemets, le mieux est de ne pas les encadrer du tout et oralce mettra tout en majuscule, ce qui est l'usage.

- Evite d'utiliser le nom "date" pour un champ qui est un mot reservé (pour le type "date"). Ce n'est pas interdit mais ça entraine parfois des comportements défaillant de pas mal d'outils (Toad notamment...)

Pour la requête, je verais bien un truc comme ça (en renomant le champ "date"):

Code :

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

1
2
3
4
5
6
7
8
select 
  max(DATE_FIC) date_dernier_fichier,
  max(NOMFICHIER) KEEP (DENSE_RANK LAST ORDER BY DATE_FIC) nom_dernier_fichier,
  TYPEFICHIER,
  sum(decode(STATUT,0,1,0)) NB_OK,
  sum(decode(STATUT,1,1,0)) NB_KO
from MATABLE
group by TYPEFICHIER

**ylarvor** · 11/06/2007, 13h06

je ne comprend pas la ligne suivante notamment KEEP:

max(NOMFICHIER) KEEP (DENSE_RANK LAST ORDER BY DATE_FIC) nom_dernier_fichier

peux tu me donner des explications ?

**marsupio49** · 11/06/2007, 13h56

Bonjour et merci de vos réponse,
remi4444, j'ai testé ta requete, en réalisant les miodifications, que tu as suggéré et cela marche nickel. Cependant tout comme ylarvor je ne comprend pas la ligne :

Code :

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

max(NOMFICHIER) KEEP (DENSE_RANK LAST ORDER BY DATE_FIC) nom_dernier_fichier

Même si cela marche j'aimerais vrailment comprendre et apprendre ce que cela signifie, ce qui me permettrait d'avancer dans ce vaste domaine.

Je ne suis également pas bien sûr de comprendre la ligne suivante :

Code :

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

sum(decode(STATUT,0,1,0)) NB_OK,

Merci beaucoup de l'aide apportée.

**McM** · 11/06/2007, 14h10

Envoyé par ylarvor

je ne comprend pas la ligne suivante notamment KEEP:

max(NOMFICHIER) KEEP (DENSE_RANK LAST ORDER BY DATE_FIC) nom_dernier_fichier

peux tu me donner des explications ?

Regarde le tuto de lalystar sur les fonctions analytiques

**marsupio49** · 11/06/2007, 14h35

Re bonjour, enfait j'ai un petit souci avec la requête, si j'essaye d'afficher l'emetteur et le destinataire (concernant le dernier fichier), la requête ne marche plus. :s

Est-ce normal?

Cordialement,

**marsupio49** · 11/06/2007, 14h47

Re bonjour, enfait j'ai un petit souci avec la requête, si j'essaye d'afficher l'emetteur et le destinataire (concernant le dernier fichier), la requête ne marche plus. :s

Est-ce normal?

De plus pour afficher le dernier fichier tu utilise le champs DATE_FIC. Le problème est qu'il y a également un champs date_heure (que je n'ai pas fait apparaître,car nous avions choisi d'utiliser le champs idtransfert qui est en auto-incrémentation, c'est ainsi plus simple).

Cordialement,

aide pour une requête complexe

Oracle

Vue hybride

Discussions similaires

Partager

Partager