最快速的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 .