如果你需要完整的行,你可以使用这个:
SELECT fullTable.nid as nid,
recent.time as time,
fullTable.user as user,
fullTable.page_id as page_id
FROM TableName fullTable
INNER JOIN (SELECT MAX(t1.time) as time, t1.user, t1.page_id
FROM TableName t1
GROUP BY user, page_id) recent
ON recent.time = fullTable.time AND
recent.user = fullTable.user AND
recent.page_id = fullTable.page_id
ORDER BY time DESC
如果您要求“group by”子句之外的列,那么mysql可以返回此组中此列的任何值.因此,如果组内的所有值都不相同,那就是您的情况,您不能直接将它包含在select子句中,您需要使用连接.
您可以在the reference上阅读有关MySQL上没有分组列的更多信息
如果您不需要nid字段,可以使用另外一个:
SELECT MAX(time) as time, user, page_id
FROM TableName
GROUP BY user, page_id
ORDER BY time DESC