MySQL ORDER BY用户在另一个表中的总行 [英] MySQL ORDER BY total rows of user in another table

问题描述

假设，我想显示一个按用户发送的邮件数量最多排序的用户列表.

Suppose, I want to show a list of users ordering by the most number of messages they have sent.

我有2个表:Users和Messages

我有10个用户

用户A发送了20条消息(消息"表中有20行)

User A sent 20 messages (have 20 rows in Messages table)

SELECT * FROM messages WHERE user='A'


User B sent 17 msgs    
User C sent 19    
User D sent 13

以此类推.

我想要一个查询，该查询将按发送的邮件总数列出用户.喜欢:

I want to a query that will list Users in order of their total messages sent. Like:

1. A (20 msgs)
2. C (19 msgs)
3. B (17 msgs)
4. D (13 msgs)

我尽力了，但是找不到任何查询来执行此操作.请帮忙.预先感谢.

I tried hard but I can't find any query to execute this. Please help. Thanks in advance.

推荐答案

SELECT user, COUNT(*) FROM messages GROUP BY user ORDER BY count(*) DESC;

这篇关于MySQL ORDER BY用户在另一个表中的总行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！