一个棘手的SQL语句

Question

1、先從b中得到每個id最后登陸的紀錄，作為c
2、a左聯接c

Answer 1

select A.id, NVL(C.value, 0.0) from A,(select id, value, Date from B bb where Date=(select max(Date) from B where aa.id=B.id group by id)) C where A.id=C.id(+);

Answer 2

create table A(id_ number(8));
create table B(id_ number(8), value_ number(8,2), date_ date);
insert into a values(1);
insert into a values(2);
insert into a values(3);
insert into a values(4);
insert into b values(1,0.1,to_date('2003-01-01','yyyy-mm-dd'));
insert into b values(1,0.2,to_date('2003-01-03','yyyy-mm-dd'));
insert into b values(3,0.5,to_date('2003-02-02','yyyy-mm-dd'));
insert into b values(3,0.1,to_date('2003-05-01','yyyy-mm-dd'));select c.id_, nvl(b.value_,0.0)  
from b, (select a.id_, max(b.date_) as date_ from a, b where a.id_=b.id_(+)  group by a.id_)  c 
where b.id_(+)=c.id_ and b.date_(+)=c.date_;       ID_ NVL(B.VALUE_,0.0)
---------- -----------------
         1                .2
         2                 0
         3                .1
         4                 0