oracle主从表 新增语句,求sql语句：同时取主表数据及从表中最新的一条数据,该如何解决...

当前位置:我的异常网» Oracle管理 » 求sql语句：同时取主表数据及从表中最新的一条数据,

求sql语句：同时取主表数据及从表中最新的一条数据,该如何解决

www.myexceptions.net  网友分享于：2013-01-14  浏览：89次

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

主表：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

谢谢大家！

------解决方案--------------------

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

文章评论