mysql - ORDER BY 另一个表的行数

Question

我必须对发表最多评论的导演进行排名。根据另一个表的行数重新排序的方法是什么？

表1(导演)

+-------------+--------------+
|    name     |    company   |  
+-------------+--------------+
| John        | John SPA     |
| Mark        | Marks Food   |
| Patrick     | Patrick & CO |
+-------------+--------------+

表 2(员工评论)

+-------------+--------------+----------+---------+
|   director  |    employee  |   Text   |  Stars  |
+-------------+--------------+----------+---------+
| John        | Omar         | ...      | 3       |
| John        | Richard      | ...      | 5       |
| Mark        | Simon        | ...      | 5       |
| Patrick     | David        | ...      | 1       |
| Patrick     | Omar         | ...      | 2       |
| John        | Simon        | ...      | 4       |
+-------------+--------------+----------+---------+

<小时/>

结果(董事排名):

1st) Dir. John [3 reviews]
2nd) Dir. Patrick [2 reviews]
3rd) Dir. Mark [1 review]
...

Answer 1

您需要加入表并选择当(ON)作者等于导演时的计数，然后根据导演结果对行进行分组，然后对其进行排序。

示例:SELECT *, COUNT(reviews.id) FROMDirectors JOIN Reviews ON Reviews.author =director.director GROUP BY direcotrs.director ORDER BY COUNT(reviews.id) DESC;