mysql - ORDER BY 另一个表的行数

Question

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

表一（主任）

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

表 2 (employee_reviews)

+-------------+--------------+----------+---------+
|   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]
...

标签： mysqlsql

Answer 1

您需要先在director.name=employee_reviews.director 上加入表格。然后你可以做select name, count(*) ... group by name。