求查寻论坛最后回复帖子的SQL语句_Oracle管理

求查找论坛最后回复帖子的SQL语句
假设有两个表，他们在数据库里面并没有建立关联。

表1：主题表,假设内容如下
topic_table

id topic

1 OOXX

2 XX00

3 ####

4 //TODO

5 。。。

6 ABC

7 CSDN

表2：回复表，假设内容如下

revert_table

id topic_id info

1 1 3333

2 2 11111

4 1 252

5 2 991

6 4 137

8 1 711

10 1 267

11 5 75

12 6 4

现在我想找到1，2，3，4这4张主题的最后一条回复（主题在回复表中ID最大的数据）而且要获取这个回复的内容，
那这个SQL语句要怎么写？只用一条SQL语句能查出来吗？

PS：我所预期找到的结果为

topic_id revert_id info

1 10 '267'

2 5 ‘991’

3 0 ''

4 6 ‘137’

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

SQL code

WITH t1 AS(SELECT 1 id,'00XX' topic FROM dualUNION ALL SELECT 2,'XX00' FROM dualUNION ALL SELECT 3,'####'FROM dualUNION ALL SELECT 4,'####'FROM dualUNION ALL SELECT 5,'####'FROM dualUNION ALL SELECT 6,'####'FROM dualUNION ALL SELECT 7,'####'FROM dual), t2 AS(SELECT 1 id,1 topic_id,3333 info FROM dualUNION ALL SELECT 2,2,11111 FROM dualUNION ALL SELECT 4,1,252 FROM dualUNION ALL SELECT 5,2,991 FROM dualUNION ALL SELECT 6,4,137 FROM dualUNION ALL SELECT 8,1,711 FROM dualUNION ALL SELECT 10,1,267 FROM dualUNION ALL SELECT 11,5,75 FROM dualUNION ALL SELECT 12,6,4 FROM dual)SELECT t1.id topic_id,Nvl(temp.id,0)revert_id,temp.info FROM t1 left join (SELECT id,topic_id,info FROM   (    SELECT id,topic_id,info,Row_Number() over (PARTITION BY topic_id ORDER BY id desc)rn FROM t2  )WHERE rn=1)temp ON t1.id=temp.topic_idORDER BY t1.id;
------解决方案--------------------
select c.topic,c.id,d.info
from revort_table d,(select a.topic,max(b.id) id
from topic_table a,revort_table b
where a.topic = b.topic
group by a.topic) c
where c.id = d.id
------解决方案--------------------
WITH t1 AS(
SELECT 1 id,'00XX' topic FROM dual
UNION ALL  
SELECT 2,'XX00' FROM dual
UNION ALL  
SELECT 3,'####'FROM dual
UNION ALL  
SELECT 4,'####'FROM dual
UNION ALL  
SELECT 5,'####'FROM dual
UNION ALL  
SELECT 6,'####'FROM dual
UNION ALL  
SELECT 7,'####'FROM dual
), t2 AS(
SELECT 1 id,1 topic_id,3333 info FROM dual
UNION ALL  
SELECT 2,2,11111 FROM dual
UNION ALL  
SELECT 4,1,252 FROM dual
UNION ALL  
SELECT 5,2,991 FROM dual
UNION ALL  
SELECT 6,4,137 FROM dual
UNION ALL  
SELECT 8,1,711 FROM dual
UNION ALL  
SELECT 10,1,267 FROM dual
UNION ALL  
SELECT 11,5,75 FROM dual
UNION ALL  
SELECT 12,6,4 FROM dual
)
select  * from t1,t2 where  t1.id = topic_id and  (t1.id,t2.id) in (
select  t1.id,max(t2.id) from t1,t2 where  t1.id = topic_id
group by t1.id)