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

2021-11-20 00:00:00 max sql distinct mysql greatest-n-per-group

我的桌子是:

<头>
id家日期时间玩家资源
11004/03/2009约翰399
21104/03/2009朱丽叶244
51204/03/2009硼酸555
31003/03/2009约翰300
41103/03/2009朱丽叶200
61203/03/2009硼酸500
71324/12/2008硼酸600
81301/01/2009硼酸700

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

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

结果是:

<头>
id家日期时间玩家资源
11004/03/2009约翰399
21104/03/2009朱丽叶244
51204/03/2009硼酸555
81301/01/2009硼酸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

不起作用.结果集有 130 行，尽管数据库有 187 行.结果包括 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

相关文章