mysql 字段 max_mysql – 如何在SQL中用另一列选择MAX(列值)...

我的表是：

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

我需要选择具有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

不行.尽管数据库保持187,但结果集有130行.

结果包括一些重复的家.

-- 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:

有各种结果.