Group By，Distinct给搞晕了，之Oracle的一个查询问题！！

[BoBo_32]

表A：
id goodsid price date
1 1001 20 2002-01-01
2 1001 30 2003-01-01
3 1001 50 2004-01-01
4 1002 15 2002-05-05
5 1002 22 2003-05-08
6 1003 5 2004-05-08
7 1003 10 2006-05-02
8 1004 11 2008-08-04
........................

表B：
goodsid goodsname
1001 A
1002 B
1003 C
1004 D
1005 E
...............

要经过查询得到：
goodsid goodsname price date
1001 A 50 2004-01-01
1002 B 22 2003-05-08
1003 C 10 2006-05-02
1004 D 11 2008-08-04

要求goodsid的价格是最近更新的！！

[DragonBill]

SELECT B.goodsid, goodsname, price, date
FROM
(
SELECT goodsid, pric,
ROW_NUMBER() OVER(PARTITION BY goodsid ORDER BY date desc) AS RNUM
FROM A
) AA
INNER JOIN B on AA.goodsid = B.goodsid
WHERE AA.RNUM = 1

用啥Group By呀

[HelloWorld_001]

select B.goodsid,B.goodsname,c.price,c.date from (select b.*
from (select goodsid,max(date) as date from A group by goodsid) a,A b
where a.goodsid=b.goodsid and a.date=b.date ) c,B
where c.goodsid=B.goodsid

[hrubber]

select *
from A join (select goodsid,price,max(date) as date from B group by goodsid,price)
using (goodsid);

[BoBo_32]

早晨脑袋清醒，一下就搞定了，不过还是要谢谢上面的朋友帮忙，呵呵！