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

Question

如何通过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: 

有各种结果。

Answer 1

最快的MySQL解决方案，没有内部查询，没有GROUP BY：SELECT m.*&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- get the row that contains the max valueFROM topten m&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- "m" from "max"&nbsp; &nbsp; LEFT JOIN topten b&nbsp; &nbsp; &nbsp; &nbsp; -- "b" from "bigger"&nbsp; &nbsp; &nbsp; &nbsp; ON m.home = b.home&nbsp; &nbsp; -- match "max" row with "bigger" row by `home`&nbsp; &nbsp; &nbsp; &nbsp; AND m.datetime < b.datetime&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- want "bigger" than "max"WHERE b.datetime IS NULL&nbsp; &nbsp; &nbsp; -- keep only if there is no bigger than max说明：使用home列加入表格。使用LEFT JOIN确保表m中的所有行都出现在结果集中。那些在表中没有匹配的那些b将具有NULLs的列b。要求的另一个条件是JOIN仅匹配列b中具有更大值datetime的行而不是来自行的行m。使用问题中发布的数据，LEFT JOIN将产生这样的对：+------------------------------------------+--------------------------------+|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; the row from `m`&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; the matching row from `b`&nbsp; &nbsp;||------------------------------------------|--------------------------------|| id&nbsp; home&nbsp; datetime&nbsp; &nbsp; &nbsp;player&nbsp; &nbsp;resource | id&nbsp; &nbsp; home&nbsp; &nbsp;datetime&nbsp; &nbsp; &nbsp; ... ||----|-----|------------|--------|---------|------|------|------------|-----|| 1&nbsp; | 10&nbsp; | 04/03/2009 | john&nbsp; &nbsp;| 399&nbsp; &nbsp; &nbsp;| NULL | NULL | NULL&nbsp; &nbsp; &nbsp; &nbsp;| ... | *| 2&nbsp; | 11&nbsp; | 04/03/2009 | juliet | 244&nbsp; &nbsp; &nbsp;| NULL | NULL | NULL&nbsp; &nbsp; &nbsp; &nbsp;| ... | *| 5&nbsp; | 12&nbsp; | 04/03/2009 | borat&nbsp; | 555&nbsp; &nbsp; &nbsp;| NULL | NULL | NULL&nbsp; &nbsp; &nbsp; &nbsp;| ... | *| 3&nbsp; | 10&nbsp; | 03/03/2009 | john&nbsp; &nbsp;| 300&nbsp; &nbsp; &nbsp;| 1&nbsp; &nbsp; | 10&nbsp; &nbsp;| 04/03/2009 | ... || 4&nbsp; | 11&nbsp; | 03/03/2009 | juliet | 200&nbsp; &nbsp; &nbsp;| 2&nbsp; &nbsp; | 11&nbsp; &nbsp;| 04/03/2009 | ... || 6&nbsp; | 12&nbsp; | 03/03/2009 | borat&nbsp; | 500&nbsp; &nbsp; &nbsp;| 5&nbsp; &nbsp; | 12&nbsp; &nbsp;| 04/03/2009 | ... || 7&nbsp; | 13&nbsp; | 24/12/2008 | borat&nbsp; | 600&nbsp; &nbsp; &nbsp;| 8&nbsp; &nbsp; | 13&nbsp; &nbsp;| 01/01/2009 | ... || 8&nbsp; | 13&nbsp; | 01/01/2009 | borat&nbsp; | 700&nbsp; &nbsp; &nbsp;| NULL | NULL | NULL&nbsp; &nbsp; &nbsp; &nbsp;| ... | *+------------------------------------------+--------------------------------+最后，该WHERE子句仅保留NULL在列中具有s的对b（它们*在上表中标记）; 这意味着，由于该JOIN子句的第二个条件，从中选择的行在列中m具有最大值datetime。阅读SQL Antipatterns：避免数据库编程的陷阱，以获取其他SQL技巧。