novembre
2009
Voici un exercice basé sur une requête…
Avec les éléments suivants :
(nolot int, prenom varchar(8), nom varchar(8));
INSERT INTO JeanMarc68 VALUES (4000, 'Albert', 'Einstein');
INSERT INTO JeanMarc68 VALUES (4000, 'Mileva', 'Einstein');
INSERT INTO JeanMarc68 VALUES (4001, 'Marie', 'Curie');
INSERT INTO JeanMarc68 VALUES (4001, 'Pierre', 'Curie');
INSERT INTO JeanMarc68 VALUES (4002, 'Albert', 'Einstein');
INSERT INTO JeanMarc68 VALUES (4003, 'Barack', 'Obama');
INSERT INTO JeanMarc68 VALUES (4004, 'Albert', 'Einstein');
INSERT INTO JeanMarc68 VALUES (4004, 'Mileva', 'Einstein ');
Le problème :
Soit la table « JeanMarc68″ contenant une colonne nolot, une colonne nom et un colonne prenom.
J’ai besoin, en entrant un nolot en paramètre de la requête, par exemple 4000, de retrouver
tous les lots ayant toutes les mêmes valeurs pour nom et prénom.
Avec la valeur 4000, le lot 4004 doit sortir parce qu’il contient Albert Einstein ET Mileva Einstein,
tout comme 4000 qui est en argument de la requête.
4002 ne devra pas sortir parce qu’il ne contient pas Mileva Einstein.
Voici une requête SQL répondant à cette question :
WITH T AS
(SELECT nom, prenom
FROM JeanMarc68
INTERSECT
SELECT nom, prenom
FROM JeanMarc68
WHERE nolot = 4000)
SELECT nolot
FROM JeanMarc68 AS JM
INNER JOIN T
ON JM.nom = T.nom
AND JM.prenom = T.prenom
GROUP BY nolot
HAVING COUNT(*) = (SELECT COUNT(*)
FROM T);
1) Pouvez vous expliquer son fonctionnement ?
2) que faut-il modifier…
…pour que d’éventuels doublons, comme le rajout de la ligne suivante :
INSERT INTO JeanMarc68 VALUES (4000, 'Albert', 'Einstein');
donne toujours la bonne réponse ?
—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *
HAVING COUNT(DISTINCT JM.nom + ‘#’ + JM.prenom) = (SELECT COUNT(*)
FROM T)
Bonjour,
Une solution équivalente, sans INTERSECT:
SELECT r.nolot <br />
FROM (SELECT t1.nolot <br />
FROM jm68 AS t <br />
INNER JOIN jm68 AS t1 <br />
ON t.nom = t1.nom <br />
AND t.prenom = t1.prenom <br />
GROUP BY t.nolot, <br />
t1.nolot, <br />
t1.prenom, <br />
t1.nom <br />
HAVING t.nolot = 4000) AS r <br />
GROUP BY r.nolot <br />
HAVING COUNT(*) = (SELECT TOP 1 COUNT(*) <br />
FROM (SELECT nom, <br />
prenom <br />
FROM jm68 <br />
WHERE nolot = 4000 <br />
GROUP BY nom, <br />
prenom)) <br />
Philippe
Bonjour Frédéric,
l’INTERSECT permet d’éliminer les doublons de l’expression de table commune.
La clause HAVING permet de vérifier que le même groupe de candidats a gagné le même lot, et donc d’éliminer le lot 4002.
Il suffit donc, pour éviter les doublons, de remplacer le = par >= dans le prédicat de la clause HAVING.
@++