MySQL连接两个表，查找最大数量和顺序 [英] MySQL join two tables, find max count and order by

问题描述

我是SQL的新手，在此之前，我仅使用简单的查询，但现在遇到了问题. 我有两张桌子.首先是评分:

I am rather newbie in SQL and before this moment I only used simple queries, but now I have a problem. I have two tables. First is rating:

id  userid  value 
1   3       +
1   2       +
1   2       +

第二个是 daybook :

id  userid  week    day     lesson  content 
1   2       1       1       6       Test!

所以现在我有一个问题.我需要:

So now I have a problem. I need to:

1)按ID连接这些表.

2)，然后根据评分表中的条目计数对结果进行排序.

2) Then order results by count of entries in rating table.

所以结果必须像这样:

userid count
3      1
2      2

该怎么做?谢谢您的帮助.

How to do that? Thanks for anything helpful.

推荐答案

select daybook.userid, count(*) as count 
    from daybook, rating 
    where daybook.userid = rating.userid
    group by daybook.userid
    order by count desc

但是您甚至根本不需要Daybook表:

But you dont even really need the daybook table:

select userid, count(*) as count
   from rating
   group by userid
   order by count desc

这篇关于MySQL连接两个表，查找最大数量和顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！