Discussion :

[DAB.cz]

J'ai les tables suivantes:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
create table tmain (
  code number constraint pk_tmain primary key,
  descr varchar2 (100));
 
create table thist (
  code number not null,
  maincode number not null,
  tdate date,
  descr varchar2 (100));
 
alter table thist
  add constraint fk_maincode
    foreign key (maincode) references tmain (code);

données:

Code :

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

1
2
3
4
5
6
7
8
9
10
insert into tmain (code, descr) values (1, 'un');
insert into tmain (code, descr) values (2, 'deux');
insert into tmain (code, descr) values (3, 'trois');
 
insert into thist (code, maincode, descr, tdate) values (1, 2, 'noir', to_date ('15.01.2008', 'dd.mm.yyyy'));
insert into thist (code, maincode, descr, tdate) values (2, 2, 'vert', null);
insert into thist (code, maincode, descr, tdate) values (3, 2, 'blanc', null);
insert into thist (code, maincode, descr, tdate) values (4, 3, 'gris', null);
insert into thist (code, maincode, descr, tdate) values (5, 3, 'rouge', to_date ('20.01.2008', 'dd.mm.yyyy'));
insert into thist (code, maincode, descr, tdate) values (6, 3, 'brun', to_date ('21.01.2008', 'dd.mm.yyyy'));

et je voudrais le résultat:

Code :

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

1
2
3
4
5
  CODE DESCR
------ -------
     1
     2 blanc
     3 gris

= chaque ligne de tmain avec le dernier "descr" de thist (dernier d'après thist.code, avec condition tdate is null)

Ma solution #1 (la meilleure que je connais):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
select m.code, h.descr
  from
    tmain m,
    thist h,
    (select distinct
        maincode,
        first_value (code) over (partition by maincode order by code desc) code
      from thist
      where tdate is null) w
  where
    w.maincode (+) = m.code and
    h.code (+) = w.code
  order by m.code;

Ma solution #2:

Code :

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

1
2
3
4
5
6
7
select m.code, h.descr
  from tmain m, thist h
  where
    h.maincode (+) = m.code and
    ((h.code = (select max (i.code) from thist i where i.maincode = m.code and i.tdate is null)) or
    (not exists (select 1 from thist i where i.maincode = m.code and i.tdate is null)))
  order by m.code;

Avez vous quelque suggestions?

[mnitu]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
Select code, (Select descr
                 From (
			Select maincode, descr, row_number() over(partition by maincode order by code desc) rn
			  From thist
		         Where tdate is null
                       ) t
                Where rn = 1
                  And m.code = t.maincode
             ) as descr
  From tmain m

ou

Code :

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

1
2
3
4
5
6
7
8
 
Select code, (Select max(descr) Keep (dense_rank last order by code)
               From thist t
              where tdate is null
                and t.maincode = m.code
              group by maincode
	     ) as descr
  From tmain m

[DAB.cz]

Merci, c'est inspirant pour moi.
Je vois un inconvénient au cas où on sélectionne plusieurs colonnes:

Code :

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

1
2
3
4
5
select m.code, h.descr1, h.descr2, h.descr3
  from ...
 
select m.code, (select descr1 ...) as descr1, (select descr2 ...) as descr2, (select descr3 ...
  from ...

Je vais tester la performance de chaque solution pour la table plus grande...

[mnitu]

Si la volumétrie des tables est importante le scalar query se comporte mal