求牛人的一句简单的sql
2张表：
A
structure： REC_NO,AID,.......
currentdata:1000 ,aaa,
B
structure： REC_NO,A_REC_NO,BID,DATE,.......
currentdata:2000 ,1000 ,bbb,10/10/2012,.....
:2001 ,1000 ,ccc,11/10/2012,.....
:2002 ,5000 ,ddd,15/10/2012,.....
关系 A.REC_NO=B.A_REC_NO
求 一条sql 输出结果集 A.AID,B.BID,其中B.BID是B.DATE 倒序第一条，其他的BID不要。
就这么简单 求高人了
[最优解释]
select a.aid,b3.bid
from a,
(select b1.a_rec_no,b1.bid
from b b1
where not exists (select 1
from b b2
where b1.a_rec_no = b2.a_rec_no
and b2.DATE > b1.DATE)) b3
where a.rec_no = b3.a_rec_no
WITH table1 AS
(
SELECT '1000' AS REC_NO, 'aaa' AS AID FROM dual
),
table2 AS
(
SELECT '2000' AS REC_NO, '1000' AS A_REC_NO,'bbb' AS BID,'10/10/2012' AS DD FROM dual
union all
SELECT '2001' AS REC_NO, '1000' AS A_REC_NO,'ccc' AS BID,'11/10/2012' AS DD FROM dual
union all
SELECT '2002' AS REC_NO, '5000' AS A_REC_NO,'ddd' AS BID,'15/10/2012' AS DD FROM dual
)
SELECT AID,BID FROM(
SELECT * FROM TABLE2 T2 INNER JOIN TABLE1 T1 ON T2.A_REC_NO = T1.REC_NO ORDER BY DD DESC) WHERE ROWNUM = 1
WITH table1 AS
(
SELECT '1000' AS REC_NO, 'aaa' AS AID FROM dual
union all
SELECT '1001' AS REC_NO, 'yyy' AS AID FROM dual
),
table2 AS
(
SELECT '2000' AS REC_NO, '1000' AS A_REC_NO,'bbb' AS BID,'10/10/2012' AS DD FROM dual
union all
SELECT '2001' AS REC_NO, '1000' AS A_REC_NO,'ccc' AS BID,'11/10/2012' AS DD FROM dual
union all
SELECT '2002' AS REC_NO, '1001' AS A_REC_NO,'ddd' AS BID,'15/10/2012' AS DD FROM dual
union all
SELECT '2003' AS REC_NO, '1001' AS A_REC_NO,'xxx' AS BID,'15/10/2012' AS DD FROM dual
)
SELECT AID,BID FROM(
SELECT * FROM TABLE2 T2 INNER JOIN TABLE1 T1 ON T2.A_REC_NO = T1.REC_NO ORDER BY DD DESC) WHERE ROWNUM = 1
SELECT AID, BID
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY A_REC_NO ORDER BY DD DESC) AS RN,
T1.AID,
T2.A_REC_NO,
T2.BID
FROM TABLE2 T2
INNER JOIN TABLE1 T1 ON T2.A_REC_NO = T1.REC_NO
ORDER BY DD DESC)
WHERE RN = 1 ORDER BY A_REC_NO
