如何通过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 | 2445  | 12  | 04/03/2009 | borat  | 5553  | 10  | 03/03/2009 | john   | 3004  | 11  | 03/03/2009 | juliet | 2006  | 12  | 03/03/2009 | borat  | 5007  | 13  | 24/12/2008 | borat  | 6008  | 13  | 01/01/2009 | borat  | 700我需要选择每个不同的home持有最大值datetime。结果将是：id  home  datetime     player   resource ---|-----|------------|--------|---------1  | 10  | 04/03/2009 | john   | 3992  | 11  | 04/03/2009 | juliet | 2445  | 12  | 04/03/2009 | borat  | 5558  | 13  | 01/01/2009 | borat  | 700我试过了：-- 1 ..by the MySQL manual: SELECT DISTINCT  home,  id,  datetime AS dt,  player,  resourceFROM topten t1WHERE datetime = (SELECT  MAX(t2.datetime)FROM topten t2GROUP BY home)GROUP BY datetimeORDER BY datetime DESC不行。结果集有130行，尽管数据库保持187.结果包括一些副本home。-- 2 ..joinSELECT  s1.id,  s1.home,  s1.datetime,  s1.player,  s1.resourceFROM topten s1JOIN (SELECT  id,  MAX(datetime) AS dtFROM toptenGROUP BY id) AS s2  ON s1.id = s2.idORDER BY datetime 不。提供所有记录。-- 3 ..something exotic: 有各种结果。

Answer 1

最快的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将具有NULLs的列b。

要求的另一个条件是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子句仅保留NULL在列中具有s的对b（它们*在上表中标记）; 这意味着，由于该JOIN子句的第二个条件，从中选择的行在列中m具有最大值datetime。

阅读SQL Antipatterns：避免数据库编程的陷阱，以获取其他SQL技巧。