最快速的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列对表进行Join操作,使用LEFT JOIN确保表m中的所有行都出现在结果集中,在表b中没有匹配项的那些对象为b中的列提供NULL s。
JOIN上的另一条条件要求只匹配b中的行,该行的值比中的行更大。
+------------------------------------------+--------------------------------+
| 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 | ... | *
+------------------------------------------+--------------------------------+