选择使用MAX和GROUP BY的所有相应的字段(Selecting all correspondi

我有这个表：

我想提出，将返回每个请求deal_id行最高的timestamp ， 以及相应的status_id 。

所以在这个例子中，我将返回2行：

1226, 3, 2009-08-18 12:10:25
1227, 2, 2009-08-17 14:31:25

我试图用这个查询做

SELECT deal_id, status_id, max(timestamp) FROM deal_status GROUP BY deal_id

但它会返回错误status_id ：

1226, 1, 2009-08-18 12:10:25
1227, 1, 2009-08-17 14:31:25

Answer 1:

没有一个单一的主键字段，我认为最好的方法是：

select * from deal_status
inner join
  (select deal_id as did, max(timestamp) as ts
  from deal_status group by deal_id) as ds
  on deal_status.deal_id = ds.did and deal_status.timestamp = ds.ts

如果允许其在同一时间同一产品的两个不同的状态，这仍然是行不通的

Answer 2:

嗨，我希望这给移动你想要它

select deal_id,status_id, timestamp from deal_status 
inner join
  (select deal_id as did,max(timestamp) as ts
  from deal_status group by deal_id  )as ds
  on deal_status.deal_id = ds.did and deal_status.timestamp = ds.ts order by deal_id

文章来源: Selecting all corresponding fields using MAX and GROUP BY