Discussion :

[jp3871]

Bonjour,

J'ai une requête qui marche presque :

j'ai une table "client" et "cmde"

Je voudrais la liste de tous les clients avec la date et prix de leur dernière commande

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT a.id_client, nom_client, prenom_client, ville_client, date_cmde, heure_cmde, prix_cmde
  FROM client a
 
  left JOIN
  (select * from
      (
           select * from cmde a order by date_cmde, heure_cmde desc
      )
      where rownum=1
  ) c
 
  on c.id_client=c.id_client and a.pays_client='FR'
 
  where a.id_client=55476

--> Pb : il me renvoie les bonnes lignes sauf que les colonnes date_cmde, heure_cmde, prix_cmde sont à null !!!

[tbc92]

order by date_cmde , heure_cmde desc --> order by date_cmde desc, heure_cmde desc
Pour avoir un tri descendant, mais de toutes façons, ce n'est pas la bonne piste.
Ici, tu exécutes cette sous-requête :

Code :

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

1
2
3
4
5
select * from
      (
           select * from cmde a order by date_cmde, heure_cmde desc
      )
      where rownum=1

Les parenthèses autour de cette sous-requête font que c'est un peu comme une boite noire, indépendante de tout ce qu'il y a autour.

Puis tu fais une jointure entre cette sous-requête et ta table client.
Lance cette sous-requête toute seule, et tu vas voir ce qu'elle renvoie, et tu vas voir que ce n'est pas la bonne piste.

Regarde dans les messages récents, il y a une discussion sur une demande très proche : Rechercher la ligne la plus récente pour chaque client.

[mnitu]

...Les parenthèses autour de cette sous-requête font que c'est un peu comme une boite noire, indépendante de tout ce qu'il y a autour.
....

Mais pas de tout, c'est juste une inline vue que peut être optimisé comme n'importe quelle autre vue par l'optimiseur d'Oracle!

Le vrai problème avec cette requête est qu'elle est erronée! Pourriez-vous voir pourquoi ? De plus la solution envisagée est inefficace!

[jp3871]

Bonjour,

Code :

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

1
2
3
4
5
6
7
left JOIN
  (select * from
      (
           select * from cmde a order by date_cmde, heure_cmde desc
      )
      where rownum=1
  ) c

le pb vient de cette sous-requête qui renvoie toutes les lignes alors que je ne veux que la ligne avec id_client
donc à l'arrivée me renvoie null

Quelle est la solution ?

[McM]

le pb vient de cette sous-requête qui renvoie toutes les lignes

Euh, non, c'est l'inverse, cette sous requête renvoie 1 seule ligne : La toute dernière commande de la table.

Pour récupérer les infos de la dernière commande d'un client, 2 solutions :
Soit une jointure entre CLIENT et CMD avec un groupement sur les clients et la fonctions KEEP

https://www.developpez.net/forums/d1.../#post10577232

Soit une jointure sur CLIENT et CMD, avec une condition sur CMD pour récupérer la dernière commande (la sous requête devant correspondre à 1 seule commande c'est à dire qu'il ne faut pas qu'il y ait 2 commandes d'un client avec la même date et heure)

Code :

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

1
2
3
4
  SELECT *
  FROM client c, cmd a
  WHERE ....
  AND (a.datecmde+heure) = (SELECT MAX (b.date_cmde+heure) FROM cmd b WHERE b.client = c.client)

[jp3871]

Bonjour,

Oui ca va marcher mais moi, je voudrais afficher le client meme si il n'a pas de facture... d'ou le left join

[tbc92]

Je t'avais parlé d'une discussion avec un thème très similaire. McM t'a donné le lien vers cette discussion.
Dans cette discussion, McM explique comment marche l'instruction KEEP DENSE_RANK ; Cette instruction correspond exactement à ton besoin.
Bon, dans la discussion en question, le besoin était de lire une seule table, mais ça s'adapte parfaitement à une requête sur plusieurs tables, même s'il y a un LEFT OUTER JOIN.

Ca va donner quelque chose comme ça :

Code :

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

1
2
3
select  client id , min(..) Keep ( dense_rank last order by date_cmd ) 
from table1 
left outer Join Table2 on ....

A toi de mettre les noms de tes tables à la place de table1 ou table2.

[jp3871]

Bonjour,

Ca fonctionne avec un CROSS APPLY mais comment faire pour avoir l'équivalent d'un LEFT JOIN car je voudrais afficher les clients même si ils n'ont pas de cmde....

[mnitu]

Regardez si le partioned Outer Join ne réponde pas à votre demande.
Pour plus d'aide fournissez un jeu d'essaye minimaliste (création des tables ,ajoute des données, etc.) avec les explications et le résultat attendu.

[jp3871]

Bonjour,

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE CLIENT
  (
    "ID_CIENT"  NUMBER,
    "CLI_NOM"   VARCHAR2(20 BYTE),
    "CLI_VILLE" VARCHAR2(20 BYTE)
  )
 
insert into client values (1,'TOTO', 'PARIS');
insert into client values (2,'TATA', 'LYON');
insert into client values (3,'TITI', 'BREST');
insert into client values (4,'TUTU', 'ARLES');

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
  CREATE TABLE CMDE
  (
    "ID_CMDE"    NUMBER,
    "ID_CLIENT"  NUMBER,
    "CMDE_TOTAL" NUMBER,
    "CMDE_DATE"  VARCHAR2(20 BYTE),
    "CMDE_HEURE" NUMBER
  )
 
insert into cmde values (1,1, 10,'20181110',142532);
insert into cmde values (1,1, 9,'20181110',142530);
insert into cmde values (1,2, 14,'20181025',193021);
insert into cmde values (1,2, 6,'20180301',92136);
insert into cmde values (1,3, 41,'20180429',203210);
insert into cmde values (1,1, 10,'20180714',214126);
insert into cmde values (1,1, 8,'20181212',132525);

Code :

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

1
2
3
4
5
6
7
8
select cli_nom,cli_ville,cmde_total,cmde_date,cmde_heure
from client a
cross apply
(
select cmde_total,cmde_date,cmde_heure,concat(cmde_date,lpad(cmde_heure,6,'0')) as fulldate from cmde b where a.id_client=b.id_client
ORDER BY fulldate DESC
    FETCH FIRST 1 rows only
)

Résultat

TOTO PARIS 8 20181212 132525
TATA LYON 14 20181025 193021
TITI BREST 41 20180429 203210

--> il me manque le dernier client TUTU car je voudrais l'afficher meme il n'a pas passé de commande avec des valeurs nulles

[mnitu]

D'abord j'ai crée une vue pour transformer les dates et heures stockées en chaine des caractères en date Oracle. L'idée d'utiliser des chaines des caractères au lieu du type date (ou similaire) est réputé dans le monde d'Oracle et pas seulement d'être une erreur! En réalité le model des données doit être revue en ce sens la vue n'étant pas vraiment une solution. Mais comme ma religion m'interdit de manipuler des dates comme des chaines des caractères j'avais pas le choix!

Code :

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

1
2
3
4
5
6
7
8
 
Create View v_CMDE As
Select id_cmde,
       id_client,
       cmde_total,
       To_Date(cmde_date||Lpad(cmde_heure,6,'0'),'YYYYMMDDHH24MISS') cmde_date_heure
  From cmde
/

Puis j'utilise juste une jointure externe et une fonction d'agrégation

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
 
SQL> Select cl.cli_nom,
  2         cl.cli_ville,
  3         Max(cmde_total) Keep (Dense_Rank Last Order By cm.cmde_date_heure) cmde_total,
  4         Max(cmde_date_heure) Keep (Dense_Rank Last Order By cm.cmde_date_heure) cmde_date_heure
  5    From client cl
  6         Left Join
  7         v_cmde cm
  8     On cm.id_client = cl.id_cient
  9    Group By cl.cli_nom, cl.cli_ville
 10    Order By 1
 11  /
 
CLI_NOM              CLI_VILLE            CMDE_TOTAL CMDE_DATE_HEURE
-------------------- -------------------- ---------- -------------------
TATA                 LYON                         14 25/10/2018 19:30:21
TITI                 BREST                        41 29/04/2018 20:32:10
TOTO                 PARIS                         8 12/12/2018 13:25:25
TUTU                 ARLES

[jp3871]

Ca fonctionne, merci de me faire découvrir cette fonction !