MySQL 如何根据列的最大值+分组从MySQL中选择行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4831491/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select rows from MySQL based on max value of a column + grouping
提问by Jim Miller
I've got a table that contains (let's say) all the times when a user looked at a specific webpage. Users can of course look at a page more than once, so there can be multiple entries for users and pages, like so:
我有一个表格，其中包含（假设）用户查看特定网页的所有时间。用户当然可以多次查看一个页面，因此用户和页面可以有多个条目，如下所示：
nid time user page_id
25 8000 4 467
24 7000 1 482
23 6000 1 484
22 5000 1 482
21 4000 5 467
20 3000 4 467
I want to do a query that returns the rows corresponding to every page viewed by every user WITH THE CATCH THAT if a user looked at a page more than once, I get the row corresponding to the most recent view (i.e., the largest value of TIME). Thus, I should get this:
我想做一个查询，返回与每个用户查看的每个页面对应的行时间）。因此，我应该得到这个：
nid time user page_id
25 8000 4 467
24 7000 1 482
23 6000 1 484
21 4000 5 467
We lose row 22 because user 1 looked at page 482 at a later time, and we lose row 20 because user 4 looked at page 467 at a later time.
我们丢失了第 22 行，因为用户 1 稍后查看了第 482 页，我们丢失了第 20 行，因为用户 4 稍后查看了第 467 页。
I almost have this figured out, but I can't quite crack it, while also convincing myself that the results I'm getting will be generally correct and not just an accident of my test cases. I keep going back and forth between GROUP BY or DISTINCT queries and embedded queries, and then my brain explodes. Any suggestions? Thanks!
我几乎已经弄清楚了，但我不能完全破解它，同时也说服自己我得到的结果通常是正确的，而不仅仅是我的测试用例的意外。我一直在 GROUP BY 或 DISTINCT 查询和嵌入式查询之间来回切换，然后我的大脑爆炸了。有什么建议？谢谢！
回答by Borja
If you need the full row you can use this:
如果你需要整行，你可以使用这个：
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
If you ask for a column outside the "group by" clause, mysql can return any value for this column inside this group. So if all the values inside the group are not the same, that is your case, you can't include it directly on the select clause, you need to use a join.
如果您要求在“group by”子句之外的列，mysql 可以返回该组内该列的任何值。因此，如果组内的所有值都不相同，那就是您的情况，您不能将其直接包含在 select 子句中，您需要使用连接。
You can read more about not grouped columns on MySQL on the reference
您可以在参考资料中阅读有关 MySQL 上未分组列的更多信息
If you don't need the nid field, you can use this other:
如果您不需要 nid 字段，您可以使用其他：
SELECT MAX(time) as time, user, page_id
FROM TableName
GROUP BY user, page_id
ORDER BY time DESC
回答by Chandu
Try this:
尝试这个：
SELECT *
FROM <YOUR_TABLE>
WHERE (user, page_id, time) IN
(
SELECT user, page_id, MAX(time) time
FROM <YOUR_TABLE>
GROUP BY user, page_id
)
回答by nybbler
SELECT nid, MAX(time), user, page_id
FROM TableName
GROUP BY nid, user, page_id
