mysql - 获取一个字段的最新值，同时对其他字段进行分组

Question

我正在尝试将大批量事务“汇总”为更易于管理、更明确的数据行。例如:

ID        item        description     qty      unitPrice    extPrice
1234      foo         foo bar          6       10            60
2345      foo         foo bar          2       10            20
3456      foo         foo bar          2       15            30

将被分组到另一个表中:

ID        item        description     qty      extPrice     lastPricePaid
1         foo         foo bar          10      110          15

我目前遇到的问题是如何确定 lastPricePaid 值。这是当前的查询不包含，其中包括:

SELECT id, groupId, datePurchase, vendor, venItem, item, itemDesc, uom, SUM(qtyPurchase) AS `qtyPurchase`, SUM(extPrice) AS `totalPrice`, MAX(unitPrice) AS `maxPrice`, unitPrice, code, stripped_venitem, MAX(datePurchase) as `maxDate`, SUM(extPrice) as `extPrice`, PONum
FROM transactions t
WHERE (((qtyPurchase)!=0))
GROUP BY groupId, vendor, venItem, item, itemDesc, uom
HAVING datePurchase > (MAX(datePurchase) - DATE_SUB(MAX(datePurchase), INTERVAL 1 YEAR));

真正的技巧是，由于我要使用 Solr，我需要将其放入单个查询中，而不需要依赖事后的处理技巧。

感谢您的见解!

Answer 1

我无法测试，但是，你尝试过按 ID 订购吗？这样您应该获得与 id 更大的单价相关的单价。

SELECT id, groupId, datePurchase, vendor, venItem, item, itemDesc, uom, SUM(qtyPurchase) AS `qtyPurchase`, SUM(extPrice) AS `totalPrice`, MAX(unitPrice) AS `maxPrice`, unitPrice, code, stripped_venitem, MAX(datePurchase) as `maxDate`, SUM(extPrice) as `extPrice`, PONum, unitPrice as lastPricePaid
FROM transactions t
WHERE (((qtyPurchase)!=0))
GROUP BY groupId, vendor, venItem, item, itemDesc, uom
HAVING datePurchase > (MAX(datePurchase) - DATE_SUB(MAX(datePurchase), INTERVAL 1 YEAR)
order by id desc