我怎么能选择行最大（列值），分区由另一列在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家datetime玩家资源
11004/03/2009约翰399
21104/03/2009朱丽叶244
51204/03/2009硼555
81301/01/2009硼700

我尝试了一个子查询，为每个home获取最大datetime：

-- 1 ..by the MySQL manual:
SELECT DISTINCThome,id,datetime AS dt,player,resourceFROM TopTen t1WHERE `datetime` = (SELECTMAX(t2.datetime)FROM TopTen t2GROUP BY home)GROUP BY `datetime`ORDER BY `datetime` DESC

结果集有130行，尽管数据库有187行，表明结果包括一些home的重复项。

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

-- 2 ..join
SELECTs1.id,s1.home,s1.datetime,s1.player,s1.resourceFROM TopTen s1JOIN (SELECTid,MAX(`datetime`) AS dtFROM TopTenGROUP BY id) AS s2ON s1.id = s2.idORDER BY `datetime`

不。给所有的记录。

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

1847356 次浏览
小开
最佳答案

你太接近了！你需要做的就是选择家庭和它的最大日期时间，然后加入两个字段上的topten表：

SELECT tt.*FROM topten ttINNER JOIN(SELECT home, MAX(datetime) AS MaxDateTimeFROM toptenGROUP BY home) groupedttON tt.home = groupedtt.homeAND tt.datetime = groupedtt.MaxDateTime
小开

这将工作，即使你有两个或更多的行为每个home与等于DATETIME的：

SELECT id, home, datetime, player, resourceFROM   (SELECT (SELECT  idFROM    topten tiWHERE   ti.home = t1.homeORDER BYti.datetime DESCLIMIT 1) lidFROM   (SELECT  DISTINCT homeFROM    topten) t1) ro, topten t2WHERE  t2.id = ro.lid
小开

我认为这会给你想要的结果：

SELECT   home, MAX(datetime)FROM     my_tableGROUP BY home

但是如果您还需要其他列，只需与原始表进行连接（检查Michael La Voie答案）

最好的问候。

小开

试试这个

select * from mytable a join(select home, max(datetime) datetimefrom mytablegroup by home) bon a.home = b.home and a.datetime = b.datetime

问候K

小开 
SELECT  tt.*FROM    TestTable ttINNER JOIN(SELECT  coord, MAX(datetime) AS MaxDateTimeFROM    rapsaGROUP BYkrd) groupedttON      tt.coord = groupedtt.coordAND tt.datetime = groupedtt.MaxDateTime
小开

这里是T-SQL版本：

-- Test dataDECLARE @TestTable TABLE (id INT, home INT, date DATETIME,player VARCHAR(20), resource INT)INSERT INTO @TestTableSELECT 1, 10, '2009-03-04', 'john', 399 UNIONSELECT 2, 11, '2009-03-04', 'juliet', 244 UNIONSELECT 5, 12, '2009-03-04', 'borat', 555 UNIONSELECT 3, 10, '2009-03-03', 'john', 300 UNIONSELECT 4, 11, '2009-03-03', 'juliet', 200 UNIONSELECT 6, 12, '2009-03-03', 'borat', 500 UNIONSELECT 7, 13, '2008-12-24', 'borat', 600 UNIONSELECT 8, 13, '2009-01-01', 'borat', 700
-- AnswerSELECT id, home, date, player, resourceFROM (SELECT id, home, date, player, resource,RANK() OVER (PARTITION BY home ORDER BY date DESC) NFROM @TestTable)M WHERE N = 1
-- and if you really want only home with max dateSELECT T.id, T.home, T.date, T.player, T.resourceFROM @TestTable TINNER JOIN(   SELECT TI.id, TI.home, TI.date,RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) NFROM @TestTable TIWHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM))TJ ON TJ.N = 1 AND T.id = TJ.id

编辑
不幸的是，MySQL中没有RANK（）OVER函数。
但它可以被模拟，见使用MySQL模拟分析（AKA排名）函数。
所以这是mysql版本：

SELECT id, home, date, player, resourceFROM TestTable AS t1WHERE(SELECT COUNT(*)FROM TestTable AS t2WHERE t2.home = t1.home AND t2.date > t1.date) = 0
小开

这适用于Oracle：

with table_max as(select id, home, datetime, player, resource, max(home) over (partition by home) maxhomefrom table)select id, home, datetime, player, resourcefrom table_maxwhere home = maxhome
小开

您也可以尝试这个，对于大表，查询性能会更好。当每个家的记录不超过两条并且它们的日期不同时，它会起作用。更好的通用MySQL查询来自上面的Michael La Voie。 

SELECT t1.id, t1.home, t1.date, t1.player, t1.resourceFROM   t_scores_1 t1INNER JOIN t_scores_1 t2ON t1.home = t2.homeWHERE t1.date > t2.date

或者在Postgres或那些提供分析函数的数据库的情况下尝试

SELECT t.* FROM(SELECT t1.id, t1.home, t1.date, t1.player, t1.resource, row_number() over (partition by t1.home order by t1.date desc) rwFROM   topten t1INNER JOIN topten t2ON t1.home = t2.homeWHERE t1.date > t2.date) tWHERE t.rw = 1
小开

由于人们似乎一直遇到这个线程（评论日期从1.5年不等）并不是这么简单：

SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home

不需要聚合功能…

干杯。

小开 
SELECT c1, c2, c3, c4, c5 FROM table1 WHERE c3 = (select max(c3) from table)
SELECT * FROM table1 WHERE c3 = (select max(c3) from table1)
小开

试试SQL服务器：

WITH cte AS (SELECT home, MAX(year) AS year FROM Table1 GROUP BY home)SELECT * FROM Table1 a INNER JOIN cte ON a.home = cte.home AND a.year = cte.year
小开

这是MySQL版本，它只打印一个条目，其中一组中有重复的MAX（datetime）。

你可以在这里测试http://www.sqlfiddle.com/#！2/0a4ae/1

样本数据

mysql> SELECT * from topten;+------+------+---------------------+--------+----------+| id   | home | datetime            | player | resource |+------+------+---------------------+--------+----------+|    1 |   10 | 2009-04-03 00:00:00 | john   |      399 ||    2 |   11 | 2009-04-03 00:00:00 | juliet |      244 ||    3 |   10 | 2009-03-03 00:00:00 | john   |      300 ||    4 |   11 | 2009-03-03 00:00:00 | juliet |      200 ||    5 |   12 | 2009-04-03 00:00:00 | borat  |      555 ||    6 |   12 | 2009-03-03 00:00:00 | borat  |      500 ||    7 |   13 | 2008-12-24 00:00:00 | borat  |      600 ||    8 |   13 | 2009-01-01 00:00:00 | borat  |      700 ||    9 |   10 | 2009-04-03 00:00:00 | borat  |      700 ||   10 |   11 | 2009-04-03 00:00:00 | borat  |      700 ||   12 |   12 | 2009-04-03 00:00:00 | borat  |      700 |+------+------+---------------------+--------+----------+

带有用户变量的MySQL版本

SELECT *FROM (SELECT ord.*,IF (@prev_home = ord.home, 0, 1) AS is_first_appear,@prev_home := ord.homeFROM (SELECT t1.id, t1.home, t1.player, t1.resourceFROM topten t1INNER JOIN (SELECT home, MAX(datetime) AS mx_dtFROM toptenGROUP BY home) x ON t1.home = x.home AND t1.datetime = x.mx_dtORDER BY home) ord, (SELECT @prev_home := 0, @seq := 0) init) yWHERE is_first_appear = 1;+------+------+--------+----------+-----------------+------------------------+| id   | home | player | resource | is_first_appear | @prev_home := ord.home |+------+------+--------+----------+-----------------+------------------------+|    9 |   10 | borat  |      700 |               1 |                     10 ||   10 |   11 | borat  |      700 |               1 |                     11 ||   12 |   12 | borat  |      700 |               1 |                     12 ||    8 |   13 | borat  |      700 |               1 |                     13 |+------+------+--------+----------+-----------------+------------------------+4 rows in set (0.00 sec)

已接受答案

SELECT tt.*FROM topten ttINNER JOIN(SELECT home, MAX(datetime) AS MaxDateTimeFROM toptenGROUP BY home) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime+------+------+---------------------+--------+----------+| id   | home | datetime            | player | resource |+------+------+---------------------+--------+----------+|    1 |   10 | 2009-04-03 00:00:00 | john   |      399 ||    2 |   11 | 2009-04-03 00:00:00 | juliet |      244 ||    5 |   12 | 2009-04-03 00:00:00 | borat  |      555 ||    8 |   13 | 2009-01-01 00:00:00 | borat  |      700 ||    9 |   10 | 2009-04-03 00:00:00 | borat  |      700 ||   10 |   11 | 2009-04-03 00:00:00 | borat  |      700 ||   12 |   12 | 2009-04-03 00:00:00 | borat  |      700 |+------+------+---------------------+--------+----------+7 rows in set (0.00 sec)
小开

最快的MySQL解决方案，没有内部查询，没有GROUP BY：

SELECT m.*                    -- get the row that contains the max valueFROM 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。

JOIN上的另一个条件要求仅匹配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反模式：避免数据库编程的陷阱书了解其他SQL技巧。

小开

为什么不使用：SELECT home， MAX（datetime）AS MaxDateTime，播放器，资源from topten GROUP BY home我错过了什么吗？

小开

这是您需要的查询：

 SELECT b.id, a.home,b.[datetime],b.player,a.resource FROM(SELECT home,MAX(resource) AS resource FROM tbl_1 GROUP BY home) AS a
LEFT JOIN
(SELECT id,home,[datetime],player,resource FROM tbl_1) AS bON  a.resource = b.resource WHERE a.home =b.home;
小开

@Michae接受的答案在大多数情况下都可以正常工作，但对于以下情况，它会失败。

如果有2行具有相同的HomeID和Datetime，则查询将返回这两行，而不是所需的不同HomeID，为此在查询中添加区分如下。

SELECT DISTINCT tt.home  , tt.MaxDateTimeFROM topten ttINNER JOIN(SELECT home, MAX(datetime) AS MaxDateTimeFROM toptenGROUP BY home) groupedttON tt.home = groupedtt.homeAND tt.datetime = groupedtt.MaxDateTime
小开

使用子查询gt每个组最近一行的另一种方法，该子查询基本上为每个组的每一行计算排名，然后过滤掉您最近的行，如排名=1

select a.*from topten awhere (select count(*)from topten bwhere a.home = b.homeand a.`datetime` < b.`datetime`) +1 = 1

演示

这是每行的排名0，以便更好地理解

通过阅读一些评论如果有两行具有相同的“home”和“datetime”字段值怎么办？

上述查询将失败，并将返回超过1行的上述情况。为了掩盖这种情况，需要另一个条件/参数/列来决定应该采取哪一行属于上述情况。通过查看示例数据集，我假设有一个主键列id应该设置为自动递增。因此，我们可以通过在CASE语句的帮助下调整相同的查询来使用此列来选择最近的行，例如

select a.*from topten awhere (select count(*)from topten bwhere a.home = b.homeand  casewhen a.`datetime` = b.`datetime`then a.id < b.idelse a.`datetime` < b.`datetime`end) + 1 = 1

演示

上面的查询将在相同的datetime值中选择具有最高id的行

可视化演示表示每行的排名

小开

希望下面的查询会给出所需的输出：

Select id, home,datetime,player,resource, row_number() over (Partition by home ORDER by datetime desc) as rownum from tablename where rownum=1
小开

（注意：对于目标列datetime不能为每个不同的home具有重复值的情况，Michael的答案是完美的。

如果你的表有#0x#1的重复行，您只需要为每个不同的#0列选择一行，这是我的解决方案：

您的表需要一个唯一的列（如id）。如果没有，请创建一个视图并向其添加一个随机列。

使用此查询为每个唯一的home值选择一行。在重复datetime的情况下选择最低的id。

SELECT tt.*FROM topten ttINNER JOIN(SELECT min(id) as min_id, home from topten tt2INNER JOIN(SELECT home, MAX(datetime) AS MaxDateTimeFROM toptenGROUP BY home) groupedtt2ON tt2.home = groupedtt2.home) as groupedttON tt.id = groupedtt.id
小开

在MySQL 8.0中，这可以通过使用带有公共表表达式的row_number（）窗口函数来有效地实现。

（这里row_number（）基本上为每个从1开始的玩家按资源降序生成唯一的序列。因此，对于序列号为1的每个玩家行，资源价值将最高。现在我们需要做的就是为每个玩家选择序列号为1的行。可以通过围绕该查询编写一个外部查询来完成。但是我们使用了通用表表达式，因为它更具可读性。）

架构：

 create  TABLE TestTable(id INT, home INT, date DATETIME,player VARCHAR(20), resource INT);INSERT INTO TestTableSELECT 1, 10, '2009-03-04', 'john', 399 UNIONSELECT 2, 11, '2009-03-04', 'juliet', 244 UNIONSELECT 5, 12, '2009-03-04', 'borat', 555 UNIONSELECT 3, 10, '2009-03-03', 'john', 300 UNIONSELECT 4, 11, '2009-03-03', 'juliet', 200 UNIONSELECT 6, 12, '2009-03-03', 'borat', 500 UNIONSELECT 7, 13, '2008-12-24', 'borat', 600 UNIONSELECT 8, 13, '2009-01-01', 'borat', 700

查询：

 with cte as(select id, home, date , player, resource,Row_Number()Over(Partition by home order by date desc) rownumber from TestTable)select id, home, date , player, resource from cte where rownumber=1

输出：

id首页日期播放器资源
1102009-03-04 00:00:00John399
2112009-03-04 00:00:00朱丽叶244
5122009-03-04 00:00:00波拉特555
8132009-01-01 00:00:00波拉特700

db<>fiddle这里

小开

如果有2条记录具有相同的日期和家，接受的答案对我不起作用。它将在连接后返回2条记录。虽然我需要选择其中的任何（随机）。此查询用作连接的子查询，因此仅限制1在那里是不可能的。这是我如何达到预期的结果。但是不知道性能。

select SUBSTRING_INDEX(GROUP_CONCAT(id order by datetime desc separator ','),',',1) as id, home, MAX(datetime) as 'datetime'from toptengroup by (home)
小开

因为这还没有发布-这在SQLServer中工作，并且是我见过的唯一不需要子查询或CTE的解决方案-我认为这是解决此类问题的最优雅的方法

  SELECT TOP 1 WITH TIES *FROM TopTenORDER BY ROW_NUMBER() OVER (PARTITION BY homeORDER BY [datetime] DESC)

关于它如何工作的一些说明-Order By子句中的Window函数将一个计数器应用于每组home值，使得具有最高[datetime]值的那个接收1。

通过SELECTingTOP 1 WITH TIES，您将选择具有第一个ROW_NUMBER值（即1）以及所有其他具有相同“绑定”ROW_NUMBER值1的记录的记录。

因此，您可以检索每个排名第一的记录的所有数据。


提交答案