MySQL选择提交视频最多的前两个人，其中type=1 - MySQL Select top two people with most submitted videos, where type=1

MySQL Select top two people with most submitted videos, where type=1

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

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

我如何将提交视频最多的前两个人用type=1进行SELECT，以便我得到这样的演示文稿?

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

我认为这将工作:

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

你可以试试:

select by_person, sum(case when type = 1 then 1 else 0 end) as NumType1
from videos v
group by by_person
order by NumType1 desc
limit 2

SELECT by_person, COUNT(title)
FROM videos
WHERE type = 1
GROUP BY by_person
ORDER BY COUNT(title) DESC LIMIT 2;