如何按user_id分组并按desc排序 [英] How to Group by user_id and order by desc

问题描述

你好，我知道这是非常基本的，但是现在我很困惑，我想按user_id按id按DESC LIMIT 4分组(我已经发布了表格和查询的简短示例)

Hello i am aware this is very basic but right now i am so confused i want to GROUP BY user_id ORDER BY id DESC LIMIT 4 (i have posted short example of table and my query)

我的表看起来像id是自动递增的，我只保存user_id，我只想只取一个user_id，而这是数据库中的最新条目，而忽略其他.

My table looks like where id is auto increment i only save user_id i only want to take one user_id only once which is lastest entry in database ignore other.

Table
---------------------
id     |   user_id  |
---------------------
13     |    25      |
12     |    36      |
11     |    25      |
10     |    42      |
9      |    95      |
8      |    25      |
7      |    95      |
---------------------
so on

我已经尝试过

SELECT * FROM  `table` GROUP BY user_id ORDER BY `id` DESC LIMIT 4

我希望它输出25,36,42,95我也尝试了许多实验，但似乎没有任何效果. 我是否需要时间戳记或将其分组保存?或哪种查询有效?

I want it to output 25,36,42,95 i have also tried many experiments but nothing seems to be working. Do i need timestamp or something to make it in group? or what query will work?

推荐答案

您正在执行的部分GROUP BY效果与预期的不同.这是产生所需结果的查询:

You are doing a partial GROUP BY which does not work the way you expect. Here is a query which produces the desired results:

SELECT MAX(id) AS MAXID, user_id
FROM `table`
GROUP BY user_id
ORDER BY MAXID DESC
LIMIT 4

该行为在此处解释:

MySQL扩展了GROUP BY的使用，以便选择列表可以引用 未在GROUP BY子句中命名的非聚合列. [...] 你可以 使用此功能避免不必要的操作以获得更好的性能 列排序和分组.但是，这主要在以下情况下有用 未在GROUP BY中命名的每个非聚合列中的所有值都是 每个小组都一样. 服务器可以自由选择任何值 每个组，所以除非它们相同，否则选择的值是 不定.此外，从每个组中选择值 不能通过添加ORDER BY子句来影响.排序 结果集是在选择值之后发生的，而ORDER BY则没有 影响服务器在每个组中选择哪个值.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. [...] You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

这篇关于如何按user_id分组并按desc排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！