求sql语句：同时取主表数据及从表中最新的一条数据

[child259]

主表：tbA
-----------------------------------
a_id a_name
1 杨天
2 张三
从表：tbB
------------------------------------
b_id b_time b_content a_id
1 2007-07-01 睡觉 1
2 2007-07-02 学习 1
3 2007-07-03 生病 1
要求取出来是这样的结果：
------------------------------------
a_id a_name b_id b_time b_content
1 杨天 3 2007-07-03 生病
2 张三 null null null

谢谢大家！

[child259]

谢谢！

[mantisXF]

SQL> select *
2 from (select tbA.a_id,
3 tbA.a_name,
4 tbB.b_id,
5 tbB.b_time,
6 tbB.b_content,
7 row_number() over(partition by tbB.a_id order by tbB.b_time desc) rn
8 from (select 1 as a_id, 'yangtian' as a_name
9 from dual
10 union all
11 select 2 as a_id, 'zhangsan' as a_name from dual) tbA,
12 (select 1 as b_id,
13 to_date('2007-07-01', 'yyyy-mm-dd') as b_time,
14 'sleep' as b_content,
15 1 as a_id
16 from dual
17 union all
18 select 2 as b_id,
19 to_date('2007-07-02', 'yyyy-mm-dd') as b_time,
20 'study' as b_content,
21 1 as a_id
22 from dual
23 union all
24 select 3 as b_id,
25 to_date('2007-07-03', 'yyyy-mm-dd') as b_time,
26 'sick' as b_content,
27 1 as a_id
28 from dual) tbB
29 where tbA.a_id = tbB.a_id(+)) tt
30 where rn = 1;

A_ID A_NAME B_ID B_TIME B_CONTENT RN
---------- -------- ---------- ----------- --------- ----------
1 yangtian 3 7/3/2007 sick 1
2 zhangsan 1

[叶子]

楼上的很强哦！