如何通过SQL中的另一列选择具有MAX（列值），DISTINCT的行？

我的表是：

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

我需要选择每个具有home最大值的不同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

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

具有各种结果。

#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

如果两个相等的最大日期时间位于同一家庭（具有不同的玩家）中，请对其进行测试，以进行区分

–马克斯姆·贡塔
09年3月4日在21:04

我认为经典的方法是使用自然连接：“ SELECT tt。* FROM topten tt NATURAL JOIN（SELECT home，MAX（datetime）AS datetime FROM topten GROUP BY home）mostrecent;”完全相同的查询，但更具可读性

–派克
10-10-22在17:42

如果两行的“ home”和“ datetime”字段值相同怎么办？

–凯末尔·杜兰（Kemal Duran）
2015年6月1日，12：10

@Young查询的问题是它可能返回给定房屋的ID，播放器和非最大行的资源，即对于home = 10，您可能会得到：3 | 10 | 2009年4月3日|约翰| 300换句话说，它不能保证结果集中一行的所有列都将属于给定home的max（datetime）。

–仙人掌
15年11月26日在12:07

关于上面的@KemalDuran的注释，如果有两行具有相同的home和datetime字段，则您需要采用Michael La Voie的解决方案并将MAX（id）AS MaxID添加到内部SELECT语句中，然后转到并在末尾添加另一行AND tt.id = groupedtt.MaxID。

–小豆蔻
17年6月21日在8:17

#2 楼

最快的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中不匹配的行将在NULL的列中具有b。

JOIN上的其他条件要求仅匹配b中在datetime上具有较大值的行。

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

+------------------------------------------+--------------------------------+
|              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       | ... | *
+------------------------------------------+--------------------------------+

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

阅读《 SQL反模式：避免数据库编程的陷阱》以获取其他SQL技巧。

使用SQLite时，如果匹配列上没有索引（即“ home”），则第一个版本比La Voie的版本要慢得多。（经过24k行测试，得出13k行）

–托马斯·坦佩尔曼（Thomas Tempelmann）
15年4月30日在10:29

这是最好的答案，如果显示执行计划，则此查询将少一步

– TlmaK0
16年8月10日在11:40

如果两行具有相同的住所和日期时间，并且日期时间是该特定住所的最大值，将会发生什么？

–艾斯提克·艾哈迈德（Istiaque Ahmed）
17年11月8日在12:57

这两行都出现在结果集中。这个答案是概念的证明。在您的实际代码中，在这种情况下，您可能还有另一个条件只能选择其中一个（也许是第一个或最后一个，或者您使用另一列来确定）。只需在ON子句中将此条件作为新条件添加即可。 F.e. ... ON ... AND m.id
– axiac
17年11月8日13:10

什么样的索引最适合这样的查询？

– AjaxLeung
19年5月26日在6:23

#3 楼

这里是T-SQL版本：

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

-- Answer
SELECT id, home, date, player, resource 
FROM (SELECT id, home, date, player, resource, 
    RANK() OVER (PARTITION BY home ORDER BY date DESC) N
    FROM @TestTable
)M WHERE N = 1

-- and if you really want only home with max date
SELECT T.id, T.home, T.date, T.player, T.resource 
    FROM @TestTable T
INNER JOIN 
(   SELECT TI.id, TI.home, TI.date, 
        RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) N
    FROM @TestTable TI
    WHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM)
)TJ ON TJ.N = 1 AND T.id = TJ.id

EDIT
不幸的是，MySQL中没有RANK（）OVER函数。
但是可以模拟，请参阅使用MySQL模拟分析（AKA排名）函数。
这是MySQL版本：

SELECT id, home, date, player, resource 
FROM TestTable AS t1 
WHERE 
    (SELECT COUNT(*) 
            FROM TestTable AS t2 
            WHERE t2.home = t1.home AND t2.date > t1.date
    ) = 0

抱歉，花花公子，＃1064-您的SQL语法错误；检查与您的MySQL服务器版本对应的手册以获取正确的语法，以在第1行附近使用'（）OVER（PARTITION BY krd ORDER BY daytime DESC）N FROM @rapsa）M WHERE N ='

–卡帕塔
09年3月5日在23:01

嗯，所以您正在使用MySQL。那就是你应该开始的！我将尽快更新答案。

–马克斯姆·贡塔
09年3月6日在7:53

@MaxGontar，您的mysql解决方案很糟糕。如果在@_TestTable中删除行＃1>，该怎么办：SELECT 1，10，'2009-03-04'，'john'，399，这就是如果给定原始值只有一行的话该怎么办？谢谢。

–egidiocs
2011年11月11日，下午3:44

BUG：将“ RANK（）”替换为“ ROW_NUMBER（）”。如果您有平局（由重复的日期值引起），您将有两个N均为“ 1”的记录。

–MikeTeeVee
13年1月10日，0：44

#4 楼

即使每个home有两行或更多行且DATETIME相等，这也将起作用：

SELECT id, home, datetime, player, resource
FROM   (
       SELECT (
              SELECT  id
              FROM    topten ti
              WHERE   ti.home = t1.home
              ORDER BY
                      ti.datetime DESC
              LIMIT 1
              ) lid
       FROM   (
              SELECT  DISTINCT home
              FROM    topten
              ) t1
       ) ro, topten t2
WHERE  t2.id = ro.lid

在表中添加了盖子字段，不好

–卡帕塔
09年3月6日在17:19

这个没有在PHPMyAdmin上执行。页面刷新，但是没有结果也没有错误..？

–卡帕塔
2009年3月24日15:47

哪里ti.home = t1.home-您能解释一下语法吗？

–艾斯提克·艾哈迈德（Istiaque Ahmed）
17年11月8日13:00

@IstiaqueAhmed：在这里您不明白的是什么？这是一个相关查询，您提到的表达式是一个相关条件。

– Quassnoi
17年11月8日在13:23

@Quassnoi，具有WHERE ti.home = t1.home行的select查询不需要定义t1的FROM子句。那么如何使用呢？

–艾斯提克·艾哈迈德（Istiaque Ahmed）
17年11月8日在16:05

#5 楼

我认为这将为您提供所需的结果：

SELECT   home, MAX(datetime)
FROM     my_table
GROUP BY home

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

最好的问候。

他还需要其他专栏。

– Quassnoi
09年3月4日在20:34

ID，家庭，日期时间，播放器，资源

– Quassnoi
09年3月6日在10:32

#6 楼

由于人们似乎一直都在使用此线程（评论日期范围为1.5年）不是那么简单：

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

不需要聚合函数...

干杯。

这似乎不起作用。错误消息：选择列表中的列'x'无效，因为它既不包含在聚合函数中也不在GROUP BY子句中。

–家禽
2011年10月25日，0：58

尽管看起来好像可以在MySQL中运行，但这绝对不能在SQL Server或Oracle中运行。

– ErikE
2014年5月22日0:44

这真的很美！这是如何运作的？通过使用DESC和默认的组返回列？因此，如果我将其更改为datetime ASC，它将返回每个房屋的最早行吗？

–未来之路
16年5月15日在22:19

这太棒了！

–狗情人
16年8月11日在12:22

如果您有未聚合的列（在MySQL中），则此方法不起作用。

– Stevey
17年4月17日在6:53

#7 楼

您也可以尝试这一操作，对于大表查询性能会更好。当每个房屋的记录不超过两个且它们的日期不同时，它将起作用。更好的一般MySQL查询是上述Michael La Voie的查询。

SELECT t1.id, t1.home, t1.date, t1.player, t1.resource
FROM   t_scores_1 t1 
INNER JOIN t_scores_1 t2
   ON t1.home = t2.home
WHERE 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) rw
 FROM   topten t1 
 INNER JOIN topten t2
   ON t1.home = t2.home
 WHERE t1.date > t2.date 
) t
WHERE t.rw = 1

这个答案正确吗？我尝试使用它，但它似乎不选择为“ home”选择最新日期的记录，而仅删除具有最早日期的记录。这是一个示例：SQLfiddle

– marcin93w
2014年7月14日14:48

@kidOfDeath-使用上下文和Postgres查询更新了我的回复

–湿婆
14年7月16日在21:02

使用SQLite时，如果匹配列上没有索引（即“ home”），则第一个版本比La Voie的版本要慢得多。

–托马斯·坦佩尔曼（Thomas Tempelmann）
15年4月30日在10:28

#8 楼

SELECT  tt.*
FROM    TestTable tt 
INNER JOIN 
        (
        SELECT  coord, MAX(datetime) AS MaxDateTime 
        FROM    rapsa 
        GROUP BY
                krd 
        ) groupedtt
ON      tt.coord = groupedtt.coord
        AND tt.datetime = groupedtt.MaxDateTime

#9 楼

这适用于Oracle：

with table_max as(
  select id
       , home
       , datetime
       , player
       , resource
       , max(home) over (partition by home) maxhome
    from table  
)
select id
     , home
     , datetime
     , player
     , resource
  from table_max
 where home = maxhome

如何选择最大日期时间？他要求按家庭分组，然后选择最大日期时间。我不知道这是怎么做到的。

–n00b
13年4月23日在21:59

#10 楼

对于SQL Server，请尝试以下操作：

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

#11 楼

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)

#12 楼

这是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.home
    FROM (
        SELECT t1.id, t1.home, t1.player, t1.resource
        FROM topten t1
        INNER JOIN (
            SELECT home, MAX(datetime) AS mx_dt
            FROM topten
            GROUP BY home
          ) x ON t1.home = x.home AND t1.datetime = x.mx_dt
        ORDER BY home
    ) ord, (SELECT @prev_home := 0, @seq := 0) init
) y
WHERE 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 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
+------+------+---------------------+--------+----------+
| 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系统。基本上，此解决方案依赖于subselect中的ORDER BY子句。此功能可能无法在各种mysql环境中使用。我没有在纯MySQL上尝试过，但是可以肯定的是，这在MariaDB 10.1上无法正常运行，如此处stackoverflow.com/questions/26372511/…所述，但是相同的代码在Percona Server上也可以正常工作。确切地说，根据t1列的数量，您可能会或可能不会获得相同的结果。

–拉德克
16 Dec 1'在9:34

该语句的示例是，当我使用t1表中的5列时，它在MariaDB 10.1上有效。一旦添加了第六列，显然会破坏原始表中的“自然”数据排序，它就会停止工作。原因是，子选择中的数据变得无序，因此多次满足“ is_first_appear = 1”条件。 Percona可以使用相同的代码和相同的数据。

–拉德克
16 Dec 1'在9:38

#13 楼

另一种方法是使用子查询来查询每组的最新行，该查询基本上计算每组的每一行的排名，然后按照rank = 1过滤出您的最新行。

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and a.`datetime` < b.`datetime`
) +1 = 1

DEMO

这里是可视化演示，用于每一行的排名，以便更好地理解

通过阅读一些注释，如果存在两行，该怎么办其中的“ home”和“ datetime”字段值相同？为了掩盖这种情况，将需要另一个标准/参数/列来确定应该采取哪种情况落在上述情况中。通过查看样本数据集，我假设存在一个主键列id，应将其设置为自动递增。因此，我们可以借助CASE语句（如

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and  case 
       when a.`datetime` = b.`datetime`
       then a.id < b.id
       else a.`datetime` < b.`datetime`
       end
) + 1 = 1

）调整相同的查询来使用此列来选择最新的行，如上图所示

DEMO

以上查询将在相同的datetime值中选择ID最高的行

视觉演示以获取每行的排名

#14 楼

为什么不使用：
选择首页，MAX（datetime）作为MaxDateTime，播放器，资源来自topten GROUP BY首页
我错过了什么吗？

这仅对MySQL有效，并且仅在5.7（？）或5.7之后的版本中禁用ONLY_FULL_GROUP_BY，因为它正在选择未聚合/聚集的列（播放器，资源），这意味着MySQL将为这些列提供随机选择的值两个结果字段。玩家列不会有问题，因为它与home列相关，但是resource列不会与home或datetime列相关，并且您不能保证将获得哪个资源值。

–简单用户
2015年11月7日下午4:26

+1的解释，但没有询问此查询在MySQL 5.6及更高版本中不会返回预期的输出，我高度怀疑它在MySQL 5.7及更高版本中的行为是否正确。

–仙人掌
15年11月26日在13:08

@ simpleuser，`玩家列不会有问题，因为它与主列相关联-您能解释更多吗？

–艾斯提克·艾哈迈德（Istiaque Ahmed）
17年11月8日在13:07

当我再次查看它时，@ IstiaqueAhmed表示该语句不正确。我本以为每个球员总是拥有相同的房屋价值，但现在我发现他们没有相同的房屋价值，因此该列也会出现相同的随机选择问题

–简单用户
17年11月8日在21:46

#15 楼

@Michae接受的答案在大多数情况下都可以正常工作，但由于以下原因而失败。

如果有两行的HomeID和Datetime相同，则查询将返回两行，所需的唯一HomeID，为此在查询中添加Distinct，如下所示。

SELECT DISTINCT tt.home  , tt.MaxDateTime
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

结果显示-“＃1054-'字段列表'中的未知列'tt.MaxDateTime'”

–艾斯提克·艾哈迈德（Istiaque Ahmed）
17年11月8日在12:33

@IstiaqueAhmed，您是否已提交MaxDatetime，即任何类似的列名..？

– Manoj Kargeti
17年11月8日在17:11

不，OP中的表没有任何此类列。

–艾斯提克·艾哈迈德（Istiaque Ahmed）
17年11月8日在17:49

错误也请说同样的话..你到底想做什么？您可以发送表结构和查询吗？

– Manoj Kargeti
17年9月9日13:52

#16 楼

试试这个

select * from mytable a join
(select home, max(datetime) datetime
from mytable
group by home) b
 on a.home = b.home and a.datetime = b.datetime

问候
K

如果两个相等的最大日期时间位于同一家庭（具有不同的玩家）中，请对其进行测试，以进行区分

–马克斯姆·贡塔
09年3月4日在21:03

max（datetime）的别名为datetime。不会有问题吗？

–艾斯提克·艾哈迈德（Istiaque Ahmed）
17年11月8日12:39

如何选择最高的日期时间？

–艾斯提克·艾哈迈德（Istiaque Ahmed）
17年11月8日在13:04

#17 楼

这是您需要的查询：

 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 b
 ON  a.resource = b.resource WHERE a.home =b.home;

你能解释你的答案吗？

–艾斯提克·艾哈迈德（Istiaque Ahmed）
17年11月8日在12:35

#18 楼

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

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

#19 楼

（注意：Michael的答案非常适合以下情况：目标列datetime不能为每个不同的home都有重复的值。）
如果表中有home x datetime的重复行，而您只需要选择一行每个不同的home列，这是我的解决方案：
您的表需要一个唯一列（例如id）。如果不是，请创建视图并向其中添加随机列。
使用此查询为每个唯一的home值选择一行。如果重复id，则选择最低的datetime。

SELECT tt.*
FROM topten tt
INNER JOIN
    (
    SELECT min(id) as min_id, home from topten tt2
    INNER JOIN 
        (
        SELECT home, MAX(datetime) AS MaxDateTime
        FROM topten
        GROUP BY home) groupedtt2
    ON tt2.home = groupedtt2.home
    ) as groupedtt
ON tt.id = groupedtt.id

编程黑洞网