MySQL 如何在mysql中使用group by按降序获取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16438255/
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 Get Data in Descending order using group by in mysql
提问by Viralk
I want table records as follows:
我想要表记录如下：
---------------------------------------------------------------------------
id | receiver_user_id | sender_user_id | message_text | insertdate
---------------------------------------------------------------------------
6 | 33 | 42 | OLD | 2013-05-08 10:08:53
8 | 33 | 43 | ddddd | 2013-05-07 19:01:45
7 | 33 | 38 | Ipsum | 2013-04-25 10:27:35
---------------------------------------------------------------------------
I am using this query :
我正在使用这个查询：
SELECT *
FROM (`player_messages`)
WHERE `receiver_user_id` = '33'
GROUP BY sender_user_id
ORDER BY `player_messages`.`insertdate` DESC
And get table records like below.
并获取如下表记录。
---------------------------------------------------------------------------
id | receiver_user_id | sender_user_id | message_text | insertdate
---------------------------------------------------------------------------
6 | 33 | 43 | ddddd | 2013-05-07 19:01:45
4 | 33 | 42 | NEW | 2013-05-07 10:08:58
7 | 33 | 38 | Ipsum | 2013-04-25 10:27:35
---------------------------------------------------------------------------
Please help. Thanks in advance.
请帮忙。提前致谢。
回答by fthiella
You are selecting all fields from your table SELECT *but you are only grouping by sender_user_id, and the values of non-aggregated columns will be untedermined and not affected by an order by.
您正在从表中选择所有字段，SELECT *但您只是按 分组sender_user_id，并且非聚合列的值将不受限制且不受 order by 的影响。
You should write your query like this:
您应该像这样编写查询：
SELECT player_messages.*
FROM player_messages
WHERE (receiver_user_id, sender_user_id, insertdate) IN (
SELECT receiver_user_id, sender_user_id, MAX(insertdate)
FROM player_messages
WHERE receiver_user_id='33'
GROUP BY receiver_user_id, sender_user_id)
ORDER BY player_messages.insertdate DESC
This will return all the rows of player_messages that have the maximum insertdate for every combination of receiver and sender.
这将返回对于接收者和发送者的每个组合具有最大插入日期的 player_messages 的所有行。
回答by Lithu T.V
When you query your MySQL database, you can sort the results by any field in an ascending or descending order by just adding 'ORDER BY' at the end of your query. You would use ORDER BY field_name ASCfor an ascending sort, or ORDER BY field_name DESCfor a descending sort.
查询 MySQL 数据库时，只需在查询末尾添加“ORDER BY”，即可按任何字段按升序或降序对结果进行排序。您可以使用ORDER BY field_name ASC进行升序排序，或使用ORDER BY field_name DESC进行降序排序。
eg
例如
"SELECT * FROM address ORDER BY name ASC"
