求牛人的一句简略的sql

求牛人的一句简单的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 &gt; b1.DATE)) b3
 where a.rec_no = b3.a_rec_no

字段名最好不要用date,date是日期类型。。
[其他解释]

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

[其他解释]

引用:

SQL code1234567891011121314WITH 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 D……

感谢回复，可是可能误解我意思了
这是我根据你代码修改的

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

[其他解释]

引用:

SQL code12345678910select 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.……

牛B啊虽然我还没有仔细看里面的结构，不过数据修改了几次与预想的是一致。
虽然现在什么也做不了，唯一的只有膜拜了
[其他解释]
不好意思。我没看全。

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

这样的结果？
1aaaccc
2yyyddd

求牛人的一句简略的sql

热点推荐