MySQL: 在group by中使用order by

Question

我有一个像这样的mysql表：

mysql> select * from pt_onhand where pn = '000A569011';
+------------+-----+----+--------+------------+---------+--------------+-----+
| pn         | pa  | mn | ACTIVE | locate     | onhand  | avg_cost     | whs |
+------------+-----+----+--------+------------+---------+--------------+-----+
| 000A569011 | P/A |    |        | AA-112     | 13.0000 | 0.0000000000|     |
| 000A569011 | P/A |    |        | PF120136.1 |  1.0000 | 5.4785156200 |     |
+------------+-----+----+--------+------------+---------+--------------+-----+

我想执行像这样的查询：

mysql> select sum(onhand),max(locate),avg_cost from pt_onhand where pn = '000A569011' group by pn;
+-------------+-------------+--------------+
| sum(onhand) | max(locate) | avg_cost     |
+-------------+-------------+--------------+
|     14.0000 | PF120136.1  | 0.0000000000|
+-------------+-------------+--------------+

所以我的问题是：我能在同一个查询中获取与max(locate) PF120136.1 相关的 avg_cost 5.4785156200 吗？谢谢

- terry

---

你需要编写一个额外的内部查询。令人失望，这在SQLite中可以正常工作，但在其他数据库管理系统中可能无法编译。因此，我不喜欢MySQL。 - nawfal

Answer 1

这个方案有些凌乱，但应该能解决问题：

select a.onhand, a.locate, p.avg_cost
from
    (select sum(onhand) onhand, max(locate) locate from pt_onhand where pn = '000A569011' group by pn) a
    join pt_onhand p on p.locate = a.locate

- Sam Peacey

Answer 2

您也可以使用子查询，例如:

select 
     sum(onhand)
    ,max(locate)
    ,(select avg_cost from pt_onhand where pn = pt.pn and locate = max(pt.locate)) as avg_cost 
from 
    pt_onhand pt 
where 
    pn = '000A569011' 
group by pn;

但是根据您的数据库大小不同，可能表现不佳，请尝试它们并查看哪个对您最有效。

- Rodders