现有一张表GoodsBook,主要字段为Goodsid,NowDate,Quantity,表中有上千种物品,每种物品至少都有一条记录,NowDate中存放的是记录入库的时间,Quantity表示物品入库的数量,我现想把GoodsBook中所有物品最后一次入库的Quantity的查出来,请问这个SQL该怎么写!!!
我原来是这样写的:
Select Distinct Goodsid,NowDate,Quantity from GoodsBook order by NowDate Desc
这样写不对啊!!!
我原来是这样写的:
Select Distinct Goodsid,NowDate,Quantity from GoodsBook order by NowDate Desc
这样写不对啊!!!
select max(NowDate)as LastDate,GoodsID from Quantity group by GoodsID) as b on a.goodsid=b.goodsid and a.nowdate=b.lastdate
begin
SQl.add('select nowDate from GoodBook order by nowDate');
Last;
date:=Fields[0].AsString;
End;
With ADoQuery2 do
begin
SQL.add('Select * from GoodBook Where NowDate='''+Date+'''');
End;
where NowDate=(select max(NowDate) from GoodsBook )
Select Distinct Goodsid,NowDate,Quantity from GoodsBook
where NowDate=(select max(NowDate) from GoodsBook )
(select goodsid,max(NowDate) as NowDate from goodsbook group by goodsid) A
left outer join goodsbook B on A.goodsid=B.goodsid and A.NowDate=B.NowDate
where goodsid=(select goodsid,max(NowDate) from GoodsBook )
Select Goodsid,NowDate,Quantity from GoodsBook where Goodsid in (select max(NowDate),Goodsid from Quantity group by GoodsID)
这个应该可以
(Select Convert(varchar,Max(NowDate),112) + '/' + GoodsID From GoodsBook Group By GoodsID)