表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的价格是最近更新的!!
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的价格是最近更新的!!
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呀
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
from A join (select goodsid,price,max(date) as date from B group by goodsid,price)
using (goodsid);