如何在MYSQL中使用另一列进行分区，选择最大值（列值）的行？

25 浏览
0 Comments

如何在MYSQL中使用另一列进行分区，选择最大值（列值）的行？

我有一个球员表现的表：

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

结果应该是：

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

我尝试过子查询获取每个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`

错误。返回所有记录。

我尝试过各种各样的奇特查询，每个查询都有不同的结果，但没有任何使我更接近解决这个问题的东西。

admin 更改状态以发布 2023年5月25日
0

2 答案

0 Comments

最快的MySQL解决方案，不使用内部查询，也不使用GROUP BY：

SELECT m.*                    -- get the row that contains the max value
FROM topten m                 -- "m" from "max"
    LEFT JOIN topten b        -- "b" from "bigger"
        ON m.home = b.home    -- match "max" row with "bigger" row by `home`
        AND m.datetime < b.datetime           -- want "bigger" than "max"
WHERE b.datetime IS NULL      -- keep only if there is no bigger than max

解释：

使用home列将表与自身连接。使用LEFT JOIN确保表m中的所有行出现在结果集中。那些在表b中没有匹配的行将在b的列中具有NULL值。

连接上的另一个条件要求只匹配b表中datetime列的值大于表m的行。

使用问题中发布的数据，LEFT JOIN将产生以下配对：

+------------------------------------------+--------------------------------+
|              the row from `m`            |    the matching row from `b`   |
|------------------------------------------|--------------------------------|
| id  home  datetime     player   resource | id    home   datetime      ... |
|----|-----|------------|--------|---------|------|------|------------|-----|
| 1  | 10  | 04/03/2009 | john   | 399     | NULL | NULL | NULL       | ... | *
| 2  | 11  | 04/03/2009 | juliet | 244     | NULL | NULL | NULL       | ... | *
| 5  | 12  | 04/03/2009 | borat  | 555     | NULL | NULL | NULL       | ... | *
| 3  | 10  | 03/03/2009 | john   | 300     | 1    | 10   | 04/03/2009 | ... |
| 4  | 11  | 03/03/2009 | juliet | 200     | 2    | 11   | 04/03/2009 | ... |
| 6  | 12  | 03/03/2009 | borat  | 500     | 5    | 12   | 04/03/2009 | ... |
| 7  | 13  | 24/12/2008 | borat  | 600     | 8    | 13   | 01/01/2009 | ... |
| 8  | 13  | 01/01/2009 | borat  | 700     | NULL | NULL | NULL       | ... | *
+------------------------------------------+--------------------------------+

最后，WHERE子句仅保留在b列中具有NULL的配对（它们在上面的表中用*标记）；这意味着，由于JOIN子句的第二个条件，从m中选择的行在datetime列中具有最大的值。

查看SQL Antipatterns：避免数据库编程的陷阱图书以了解其他SQL技巧。

0
0 Comments

你已经非常接近成功！你只需要选择房屋和它的最大日期时间，然后在这两个字段上与 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

0