mysql - 如何在 MYSQL 中选择具有 MAX(列值)和 PARTITION 的行？

Question

我有一张球员表现表:

CREATE TABLE TopTen (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  home INT UNSIGNED NOT NULL,
  `datetime`DATETIME NOT NULL,
  player VARCHAR(6) NOT NULL,
  resource INT NOT NULL
);

什么查询将返回每个不同的 home 保持其最大值 datetime 的行？换句话说，我如何按最大 datetime 过滤(按 home 分组)并仍然包含其他非分组、非聚合列(例如 player ) 在结果中？

对于这个示例数据:

INSERT INTO TopTen
  (id, home, `datetime`, player, resource)
VALUES
  (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)
;

结果应该是:

<头>

身份证	家	日期时间	玩家	资源
1	10	04/03/2009	约翰	399
2	11	04/03/2009	朱丽叶	244
5	12	04/03/2009	硼砂	555
8	13	01/01/2009	硼砂	700

我尝试使用子查询获取每个 home 的最大 datetime:

-- 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

结果集有 130 行，虽然数据库有 187 行，表明结果包含 home 的一些重复项。

然后我尝试加入一个子查询，该子查询为每一行 id 获取最大 datetime:

-- 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`

不。提供所有记录。

我尝试了各种奇特的查询，每一个都有不同的结果，但没有什么能让我更接近解决这个问题。

Answer 1

你离得太近了!您需要做的就是选择住宅及其最大日期时间，然后在两个字段中加入 topten 表:

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