如何通过MYSQL中的另一列来选择具有MAX(列值)，PARTITION的行?

本文介绍了如何通过MYSQL中的另一列来选择具有MAX(列值)，PARTITION的行?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的表格是:

id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 3 | 10 | 03/03/2009 | john | 300 4 | 11 | 03/03/2009 | juliet | 200 6 | 12 | 03/03/2009 | borat | 500 7 | 13 | 24/12/2008 | borat | 600 8 | 13 | 01/01/2009 | borat | 700

我需要选择每个具有 datetime 最大值的不同的 home .

I need to select each distinct home holding the maximum value of datetime.

结果将是:

id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 8 | 13 | 01/01/2009 | borat | 700

我已经尝试过:

-- 1 ..by the MySQL manual: SELECT DISTINCT home, id, datetime AS dt, player, resource FROM topten t1 WHERE datetime = (SELECT MAX(t2.datetime) FROM topten t2 GROUP BY home) GROUP BY datetime ORDER BY datetime DESC

不起作用.尽管数据库保留187个，但结果集有130行.结果包括 home 的一些重复项.

Doesn't work. Result-set has 130 rows although database holds 187. Result includes some duplicates of home.

-- 2 ..join SELECT s1.id, s1.home, s1.datetime, s1.player, s1.resource FROM topten s1 JOIN (SELECT id, MAX(datetime) AS dt FROM topten GROUP BY id) AS s2 ON s1.id = s2.id ORDER BY datetime

不.给出所有记录.

-- 3 ..something exotic:

具有各种结果.

推荐答案

您是如此亲密！您需要做的就是选择住所及其最大日期时间，然后在两个字段上重新加入 topten 表:

You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields:

SELECT tt.* FROM topten tt INNER JOIN (SELECT home, MAX(datetime) AS MaxDateTime FROM topten GROUP BY home) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime