Requête avec un ensemble de paramètres

**aelmalki** · 02/11/2012, 00h45

Bonjour,

Prenons l'exemple de Clients/Commandes, un client a plusieurs commandes.

Code :

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

1
2
3
4
5
6
 
T_CLIENT
ID | CODE_CLI
1  | CLIENT_A
2  | CLIENT_B
3  | CLIENT_C

Code :

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

1
2
3
4
5
6
7
8
9
10
 
T_COMMANDE
ID | ID_CLT | CODE_COM
1  |   1    | Souris
2  |   1    | Clavier
3  |   2    | Souris
4  |   2    | Disque dur
5  |   3    | Clavier
6  |   3    | Souris
7  |   3    | Ecran

Je veux une requête SQL qui me permettra de récupérer les clients via les deux entrées "Souris" ET "Clavier". En fait, je dois extraire les clients qui ont commandé des souris et claviers à la fois.

Est ce que c'est possible avec SQL ? si oui, pouvez-vous me donner une piste ?

Merci pour votre aide.

**SQLpro** · 02/11/2012, 10h32

SQL étant un langage complet au sens de Turing, il n'y a aucune limite d'utilisation du langage pour résoudre n'importe quel problème bien posé...
À lire : http://fr.wikipedia.org/wiki/Turing-complet

Pour votre problème il existe différentes solutions...

1) faire deux requêtes et une intersection :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT C.*
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.ID_CLI
WHERE  M.CODE_COM = 'Souris'
INTERSECT
SELECT C.*
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.ID_CLI
WHERE  M.CODE_COM = 'Clavier'

2) demander ceux qui ont commandé les deux objets et compter si ce sont bien au moins deux objets différents :

Code :

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

1
2
3
4
5
6
7
SELECT C.*
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.ID_CLI
WHERE  M.CODE_COM IN ('Souris', 'Clavier')
GROUP  BY C.ID, C.CODE_CLI
HAVING COUNT(DISTINCT CODE_COM) >= 2;

Et pour vous former au langage SQL, mon site, comme mon bouquin, peuvent vous y aider !

A +

**aelmalki** · 02/11/2012, 11h02

Bonjour,

Merci pour votre retour,

Je constate que j'ai oublié d'ajouter un enregistrement pour le client 3 pour avoir les commandes : souris, clavier et écran. Mon premier post est mis à jour.

En essayant la deuxième solution, qui est pour moi la plus simple (surtout que je peux avoir des dizaines de commandes dans la recherche), je n'arrive pas à avoir le résultat attendu qui est le client 1 parce que c'est le seul qui à commandé les deux objets 'Souris' et 'Clavier' seulement.

A votre avis, existe il une autre piste ?

Merci beaucoup.

**SQLpro** · 02/11/2012, 15h39

dans ce cas ce n'est pas >= 2, mais =2 qu'il faut mettre dans le filtre de résultat HAVING...

A +

**StringBuilder** · 02/11/2012, 17h47

Dites moi si je me trompe, mais à partir du moment où on a :
- Une clause T_CLIENT AS C INNER JOIN T_COMMANDE AS M dans le FROM.
- Une clause M.CODE_COM IN ('Souris', 'Clavier') dans le WHERE.
- Une clause COUNT(DISTINCT CODE_COM) dans le HAVING.

Qu'on mette => 2 ou = 2 ça devrait faire exactement la même chose puisque COUNT(DISTINCT CODE_COM) sera obligatoirement égal à 1 ou 2 (0 ne serait présent que si T_CLIENT AS C INNER JOIN T_COMMANDE AS M était remplacé par T_CLIENT AS C LEFT OUTER JOIN T_COMMANDE AS M et des valeurs suppérieures à 2 ne seraient présentent que si M.CODE_COM IN ('Souris', 'Clavier') n'était pas présent.

Donc à part une erreur de compilation de la requête qu'aelmalki s'est bien gardé de nous préciser, je ne vois pas en quoi mettre = 2 à la place de => 2 pourrait bien changer.

**aelmalki** · 02/11/2012, 17h59

@StringBuilder : je suis pour ton analyse, aucun changement avec l'=, parce que les clients 1 et 3 ont les deux 'Souris' et 'Clavier' comme commandes.

**StringBuilder** · 02/11/2012, 18h05

En fait, ce que tu veux, c'est les clients qui ont commandé une souris et un clavier et rien d'autre, c'est ça ?

Dans ce cas, je dirais qu'il faut user de subtilité :

Code sql :

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

1
2
3
4
5
6
7
 
SELECT C.ID
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.ID_CLI
GROUP  BY C.ID, C.CODE_CLI
HAVING sum(case CODE_COM when 'Clavier' then 0 when 'Souris' then 0 else 1 end) = 0 and sum(distinct code_dom) = 2;

On peut certainement faire ça de façon bien différente.

C'est à dire qu'on vérifie deux choses :
- Que rien d'autre que "Clavier" et "Souris" ont été commandés
- Qu'il y a 2 et seulement 2 références différentes qui ont été commandées

**aelmalki** · 02/11/2012, 18h21

Merci pour votre retour, oui c'est exactement ce que je veux.

Je suis novice dans SQL, donc je prends vos réponses et j'essaye de comprendre un peu comment les implémenter.

Si je met cette instruction :

Code :

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

HAVING sum(case CODE_COM when 'Clavier' then 0 when 'Souris' then 0 else 1 end) = 0

Est ce que cette instruction devient alors inutile :

Code :

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

AND sum(DISTINCT code_dom) = 2

Ce que j'ai compris : c'est que avec les cases, on récupère seulement les clients avec 'Clavier' et 'Souris', rien de plus.

Qu'en pensez-vous ?

**skuatamad** · 02/11/2012, 18h30

Envoyé par aelmalki

Est ce que cette instruction devient alors inutile :

Code :

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

AND sum(DISTINCT code_dom) = 2

Ben non, sans cette clause, un client peut n'avoir commandé qu'une souris ou qu'un clavier mais pas les 2, par contre c'est un COUNT et pas une SUM.

**aelmalki** · 02/11/2012, 18h49

@skuatamad : oui vous avez raison, bien vu pour le COUNT au lieu du SUM.

En gros apparemment ça marche sur cet exemple, je vous remercie tous pour votre aide, je viens d'apprendre beaucoup de chose en SQL.

Merci.

**SQLpro** · 03/11/2012, 16h04

Plus optimisé :

Code :

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

1
2
3
4
5
6
7
8
SELECT C.*
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.ID_CLI
WHERE  M.CODE_COM IN ('Souris', 'Clavier')
  AND  (SELECT COUNT(DISTINCT CODE_COM)
        FROM   T_COMMANDE AS CD
        WHERE  CD.ID_CLI = C.ID) = 2;

A +

**Fred_34** · 05/11/2012, 20h42

Envoyé par SQLpro

Plus optimisé :

Code :

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

1
2
3
4
5
6
7
8
SELECT C.*
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.ID_CLI
WHERE  M.CODE_COM IN ('Souris', 'Clavier')
  AND  (SELECT COUNT(DISTINCT CODE_COM)
        FROM   T_COMMANDE AS CD
        WHERE  CD.ID_CLI = C.ID) = 2;

La même en corrigée :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT C.ID
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.ID_CLT
WHERE  M.CODE_COM IN ('Souris', 'Clavier')
  AND  (SELECT COUNT(DISTINCT CODE_COM)
        FROM   T_COMMANDE AS CD
        WHERE  CD.ID_CLT = C.ID) = 2
GROUP BY C.ID
HAVING COUNT(DISTINCT CODE_COM)=2

Je ne sais pas si elle est plus performante que celle de StringBuilder.

**StringBuilder** · 06/11/2012, 21h21

Une autre solution, et cette fois ça devrait être ce qu'il y a de plus performant :

Code sql :

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

1
2
3
4
5
6
7
 
select c.id
from t_client c
inner join t_commande m on m.id_cli = c.id and m.code in ('Souris', 'Clavier')
where not exists (select null from t_commande z where z.id_cli = c.id and z.code not in ('Souris', 'Clavier'))
group by c.id
having count(distinct m.code) = 2;

**StringBuilder** · 06/11/2012, 21h47

(Grmpf)

Je viens de faire un test sur SQL Server 2012 Express.

Voici le script :

Code sql :

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
45
46
47
48
49
50
51
52
53
54
55
 
create table t_client
(
	id int identity not null primary key,
	code_cli varchar(10) not null
);
 
create table t_commande
(
	id int identity not null primary key,
	id_clt int not null references t_client(id),
	code_com varchar(10) not null
);
 
insert into t_client (code_cli) values 
('Client 1'), 
('Client 2'), 
('Client 3'), 
('Client 4'), 
('Client 5');
 
insert into t_commande (id_clt, code_com) values 
(1, 'Clavier'), (1, 'Souris'), (1, 'Ecran'), (1, 'Clavier'), (1, 'Souris'), 
(2, 'Clavier'), (2, 'Souris'), (2, 'Clavier'), 
(3, 'Clavier'), (3, 'Clavier'), 
(4, 'Ecran'), (4, 'Clavier'), 
(5, 'Souris'), (5, 'Clavier');
 
-- Première proposition de StringBuilder
SELECT C.ID
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.id_clt
GROUP  BY C.ID
HAVING sum(case code_com when 'Clavier' then 0 when 'Souris' then 0 else 1 end) = 0 AND count(DISTINCT code_com) = 2;
 
-- Première proposition de Fred_34
SELECT C.ID
FROM   T_CLIENT AS C
       INNER JOIN T_COMMANDE AS M
             ON C.ID = M.id_clt
WHERE  M.code_com IN ('Souris', 'Clavier')
  AND  (SELECT COUNT(DISTINCT code_com)
        FROM   T_COMMANDE AS CD
        WHERE  CD.id_clt = C.ID) = 2
GROUP BY C.ID
HAVING COUNT(DISTINCT code_com)=2;
 
-- Seconde proposition de StringBuilder
SELECT c.id
FROM t_client c
INNER JOIN t_commande m ON m.id_clt = c.id AND m.code_com IN ('Souris', 'Clavier')
WHERE NOT EXISTS (SELECT NULL FROM t_commande z WHERE z.id_clt = c.id AND z.code_com NOT IN ('Souris', 'Clavier'))
GROUP BY c.id
HAVING count(DISTINCT m.code_com) = 2;

Aucune des requêtes de SQLPro n'y figurent, car y'en a aucune qui retourne le bon résultat (pas en forme en ce moment...)

Voici le plan d'exécution du lot en pièce jointe.

Chose assez surprenante, ma première requête est la plus performance, talonnée par ma seconde proposition.
Quant à la proposition de Fred_34 basée sur la suggestion de SQLPro, elle prend à elle toute seule plus de la moitié du lot.

Moralité : j'ai peut-être pour habitude de travailler avec un MCD de merde et un SGBD qui vaut pas mieux, mais je sais tout de même écrire des requêtes performantes et qui marchent.
Oui, je sais, le quidam est aigri, mais il est blessé.

**StringBuilder** · 06/11/2012, 21h58

Pour mettre les requêtes dans une situation plus réaliste, j'ai aussi ajouté l'index suivant :

Code sql :

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

1
2
 
create index ix_code_com on t_commande (code_com);

J'ai pas regardé en détail l'impact sur les plans d'exécution, mais les proportions restent les mêmes.

En revanche, avec l'index suivant, on a des changements intéressants :

Code sql :

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

1
2
 
create index ix_code_com on t_commande (code_com, id_clt);

En pièce jointe les nouveaux plans d'exécution.

On remarque que ma première requête est alors complètement à la ramasse (elle passe à près de 50% du lot) alors que ma seconde ainsi que celle de Fred_34 deviennent à peu de chose aussi performantes l'une que l'autre.

Etant donné que la seconde requête est dans les deux cas autour de 25% du lot, et donc qu'elle tire aussi bien parti de la présence d'index que de leur absence, je pense qu'il sera judicieux de privilégier cette syntaxe, qui a en plus l'avantage de couler de source.

-- Edit : Je remarque aussi que la version "NOT IN" est légèrement plus performante que la version "NOT EXISTS" :

Code sql :

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

1
2
3
4
5
6
7
 
SELECT c.id
FROM t_client c
INNER JOIN t_commande m ON m.id_clt = c.id AND m.code_com IN ('Souris', 'Clavier')
WHERE c.id not in (SELECT z.id_clt FROM t_commande z WHERE z.id_clt = c.id AND z.code_com NOT IN ('Souris', 'Clavier'))
GROUP BY c.id
HAVING count(DISTINCT m.code_com) = 2;

**islamov2000** · 07/11/2012, 12h33

Un autre principe, en enrichissant le jeu de données.

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
with T_CLIENT as
(select 1 ID,  'CLIENT_A'CODE_CLI from dual union
select 2 ,  'CLIENT_B' from dual union
select 3,   'CLIENT_C' from dual union
select 4,   'CLIENT_D' from dual
), 
T_COMMANDE as
(
select 1 ID,    1 ID_CLT    ,'Clavier' CODE_COM from dual union
select 2     ,1     ,'Souris' from dual union
select 3     ,2     ,'Souris' from dual union
select 4     ,2     ,'Disque dur' from dual union
select 5     ,3     ,'Clavier' from dual union
select 6     ,3     ,'Souris' from dual union
select 7     ,3     ,'Ecran' from dual union
select 8     ,4     ,'Souris' from dual union
select 9     ,4     ,'Clavier' from dual
 
)
 
 
 
 
select  C.ID_CLT,C.CODE_CLI from
(
select B.ID_CLT,A.CODE_CLI ,
 rtrim(REPLACE(REPLACE(XMLAgg(XMLElement("x", B.CODE_COM  ) ORDER BY B.CODE_COM ASC), '<x>', ''), '</x>', '+'), '+') article
 from T_client A join T_commande B on  A.ID=B.ID_CLT
 group by B.ID_CLT,A.CODE_CLI 
 
 )C
 where C.article='Clavier+Souris'

Bon courage