php - MySQL 选择提交视频最多的前两个人，其中 type=1

Question

我有一张由人们提交的视频表:

+----+-----------+------+---------+
| id | by_person | type | title   |
+----+-----------+------+---------+
|  1 |         3 |    1 | title1  |
|  2 |         4 |    1 | title2  |
|  3 |         3 |    1 | title3  |
|  4 |         4 |    2 | title4  |
|  5 |         3 |    1 | title5  |
|  6 |         6 |    2 | title6  |
|  7 |         6 |    2 | title7  |
|  8 |         4 |    2 | title8  |
|  9 |         3 |    1 | title9  |
| 10 |         4 |    1 | title10 |
| 11 |         4 |    1 | title11 |
| 12 |         3 |    1 | title12 |
+----+-----------+------+---------+

如何SELECT提交最多 type=1 视频的前两名人员，以便我得到这样的演示文稿？

1. Person(3) - 5 videos
2. Person(4) - 3 videos

Answer 1

我认为这会起作用:

SELECT by_person, count(*) AS total
FROM videos
WHERE type = 1
GROUP BY by_person
ORDER BY total DESC
LIMIT 2

演示:http://sqlfiddle.com/#!2/b2916/22