求一条SQL语句

[jxjasap520]

需求:只列出零件的最新采购价格
已知:两个表订单主表[orders](orderid,orderdate)订单明细表(orderid,productid,unitprice)

[fibbery]

select bb.productid,bb.unitprice
from a aa JOIN b bb ON a.orderid=b.orderid
where aa.orderdate=(select max(aaa.orderdate) from a aaa JOIN b bbb ON aaa.orderid=bbb.orderid where bbb.productid=bb.productid)

试一试，我没试过

[jhone99]

select a.unitprice from 订单明细表 a,订单主表 b
where a.orderid = b.orderid
and b.orderdate =(select max(orderdate) from 订单主表)

[feiyun0112]

seelct a.*,b.*
from
orders a
inner join 订单明细表 b
on a.orderid=b.orderid
inner join
(
select max (a.orderdate) as orderdate,b.productid
from
orders a
inner join 订单明细表 b
on a.orderid=b.orderid
group by b.productid
) x
on x.orderdate =a.orderdate and x.productid=b.productid

*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码)

http://feiyun0112.cnblogs.com/