使用 MAX 和 GROUP BY 选择所有相应的字段

织梦狗教程数据库问题

2021-01-01

Selecting all corresponding fields using MAX and GROUP BY(使用 MAX 和 GROUP BY 选择所有相应的字段)

本文介绍了使用 MAX 和 GROUP BY 选择所有相应的字段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有这张桌子:

我想发出一个请求，该请求将为每个 deal_id 返回具有最高 timestamp、 和相应 status_id.


And I would like to make a request that would return for each deal_id the row with the highest timestamp, and the corresponding status_id.
所以对于这个例子，我会返回 2 行:
So for this example, I would have returned 2 rows :
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


推荐答案
如果没有一个主键字段，我认为最好的选择是:
without a single primary key field, I think your best bet is:
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

如果您允许同一产品同时具有两种不同的状态，这仍然不起作用
this still won't work if you allow having two different statuses for the same product at the same time

                        这篇关于使用 MAX 和 GROUP BY 选择所有相应的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！