mysql - 从表中获取最新日期及其在mysql中的行

Question

我有 2 个表: Assets 和库存。

表格资源

表库存

当我运行以下 mysql 查询时:

SELECT a.assetnum,a.description,a.user,MAX(b.invdate) as invdate , b.note 
FROM asset a 
LEFT JOIN inventory b on a.assetnum=b.assetnum 
GROUP BY a.assetnum, a.description

我得到的是这样的:

但是，应该是这样的......

我的 mysql 查询出了什么问题？有人可以帮我吗...

Answer 1

您基本上会在结果中得到一个随机的注释。您可能需要这样的东西:

 select a.assetnum,a.description,a.model, i.invdate, i.note 
 from inventory i
 join
 (
   select assetnum, max(invdate) max_invdate
   from inventory
   group by assetnum
 ) t on i.assetnum = t.assetnum and
        i.invdate = t.max_invdate
 right join asset a on a.assetnum = i.assetnum