主题：[求助]如何获取最后一次采购信息

SEL ECT     a.FName AS 物料名称, a.FModel AS 规格型号, a.FNumber AS 物料代码, a.FItemID AS 物料ID, c.FFullNumber AS 供应商代码
FROM         AIS20181207194554.dbo.t_ICItemCore AS a LEFT OUTER JOIN ‘物料表
                      AIS20181207194554.dbo.POOrderEntry AS b ON a.FItemID = b.FItemID LEFT OUTER JOIN ’采购订单明细表
                      AIS20181207194554.dbo.POOrder AS d ON d.FInterID = b.FInterID LEFT OUTER JOIN  ‘采购订单总表
                      AIS20181207194554.dbo.t_Item AS c ON d.FSupplyID = c.FItemID '供应商信息表

 

以上代码获得的供应商代码并非是最后一次采购的，如何获得最后一次采购的供应商代码。

1、要先分组获得每个分组最后一次采购信息，如

 

select 某列, max(日期) as 最后日期 from 某表 group by 某列 

 

2、然后得到全部信息，如

 

select * from (表1) as a inner join 某表 as b on a.某列=b.某列 and a.最后日期=b.日期

按你的思路

 

SE LECT     a.FItemID AS 物料ID, a.FNumber AS 物料代码, a.FName AS 物料名称, a.FModel AS 规格型号, c.FName AS 供应商, d.FDate AS 下单日期
FROM         AIS20181207194554.dbo.t_ICItemCore AS a LEFT OUTER JOIN
                      AIS20181207194554.dbo.POOrderEntry AS b ON a.FItemID = b.FItemID LEFT OUTER JOIN
                      AIS20181207194554.dbo.POOrder AS d ON d.FInterID = b.FInterID LEFT OUTER JOIN
                      AIS20181207194554.dbo.t_Item AS c ON d.FSupplyID = c.FItemID INNER JOIN
                          (SE LECT     X.FItemID AS 物料ID, MAX(Y.FDate) AS 下单日期
                            FROM          AIS20181207194554.dbo.POOrderEntry AS X LEFT OUTER JOIN
                                                   AIS20181207194554.dbo.POOrder AS Y ON X.FInterID = Y.FInterID
                            GROUP BY X.FItemID) AS v ON v.下单日期 = d.FDate AND v.物料ID = a.FItemID

 

但结果还不是我想要的，不知那里出问题了。

 

此主题相关图片如下：无标题.png

SEL ECT  top 1   a.FName AS 物料名称, a.FModel AS 规格型号, a.FNumber AS 物料代码, a.FItemID AS 物料ID, c.FFullNumber AS 供应商代码
FROM         AIS20181207194554.dbo.t_ICItemCore AS a LEFT OUTER JOIN ‘物料表
                      AIS20181207194554.dbo.POOrderEntry AS b ON a.FItemID = b.FItemID LEFT OUTER JOIN ’采购订单明细表
                      AIS20181207194554.dbo.POOrder AS d ON d.FInterID = b.FInterID LEFT OUTER JOIN  ‘采购订单总表
                      AIS20181207194554.dbo.t_Item AS c ON d.FSupplyID = c.FItemID

这句代码，才能得到最后一条数据

 

select * from (select 某列, max(日期) as 最后日期 from 某表 group by 某列 ) as a inner join 某表 as b on a.某列=b.某列 and a.最后日期=b.日期

 

请先尝试单独执行这句代码看看效果啊

 

得到这个表以后，也就是得到了每个分组的最后一条数据。接着，你和供应商表再连接，即可得到值啊