表一 temp1
login_id name post_name
1 张三 java
2 李四 .net
表二 temp2
login_id name pro_post_name result desc time
1 张三 java 1 。。。 2012-05-12
1 张三 java 2 。。。 2012-06-05
2 李四 java 2 。。。 2012-05-22
2 李四 .net 2 。。。 2012-05-12
要做的是根据表一中的 login_id 和 post_name 查询出来 表二中对应的 reuslt 和 desc
我的做法是
select
a.login_id,
a.name,
a.post_name,
(select b.result from temp2 b where b.login_id =a.login_id and b.pro_post_name=a.post_name and ROWNUM <=1 order by time desc ),
(select b.desc from temp2 b where b.login_id =a.login_id and b.pro_post_name=a.post_name and ROWNUM <=1 order by time desc )
from temp1 a
如果单独写一条SQL
select b.desc from temp2 b where b.login_id ='1'and b.pro_post_name='java'and ROWNUM <=1 order by time desc
可以查询到数据，但是写成两个表关联起来 就差不多了。
求解。
------解决方案--------------------
- SQL code
with temp1(login_id,name,post_name) as(select 1,'张三','java' from dualunion all select 2,'李四','.net' from dual),temp2(login_id,name,pro_post_name,result,"desc",time) as(select '1','张三','java',1,'desc1',to_date('2012-05-12','yyyy-mm-dd') from dualunion all select '1','张三','java',2,'desc2',to_date('2012-06-05','yyyy-mm-dd') from dualunion all select '2','李四','java',2,'desc3',to_date('2012-05-22','yyyy-mm-dd') from dualunion all select '2','李四','.net',2,'desc4',to_date('2012-05-12','yyyy-mm-dd') from dual)select a.login_id,a.name,a.post_name,b.result,b."desc"from temp1 ainner join temp2 b on a.login_id=b.login_id and a.post_name=b.pro_post_name and b.time=(select max(time) from temp2 c where c.login_id=b.login_id and c.pro_post_name=b.pro_post_name);/* LOGIN_ID NAME POST_NAM RESULT desc ---------- ------------ -------- ---------- ---------- 1 张三 java 2 desc2 2 李四 .net 2 desc4 */