在SQLite中选择前十名

I have a table which records questions, their answers, and their authors. The column names are as follows:

我有一个表格，记录问题、他们的答案和作者。列名如下:

id, question, answers, author

id、问题答案,作者

I would like to get a list of top 10 authors who have written the most questions. So it would need to first count the number of questions each author has written then sort them by the count then return the top 10.

我想要一份前10位写了最多问题的作家的名单。因此，它需要首先计算每个作者所写的问题的数量，然后按计数排序，然后返回前10。

This is in SQLite and I'm not exactly sure how to get the list of counts. The second part should be fairly simple as it's just an ORDER BY and a LIMIT 10. How can I get the counts into a list which I can select from?

这是在SQLite中，我不确定如何获得计数列表。第二部分应该相当简单，因为它只是一个ORDER BY和LIMIT 10。如何将计数输入到我可以选择的列表中?

3 个解决方案

#1

SELECT BY COUNT(author)
    ,author
FROM table_name
GROUP BY author
ORDER BY COUNT(author) DESC LIMIT 10;

#2

You can apply an order by clause to an aggregate query:

您可以对聚合查询应用order by子句:

SELECT   author, COUNT(*)
FROM     mytable
GROUP BY author
ORDER BY 2 DESC
LIMIT    10

#3

You could wrap your query as a subquery and then use LIMIT like this:

您可以将查询包装为子查询，然后使用如下限制:

SELECT *
FROM (
    SELECT author
        ,COUNT(*) AS cnt
    FROM mytable
    GROUP BY author
    ) t
ORDER BY t.cnt DESC 
LIMIT 10;

#1

SELECT BY COUNT(author)
    ,author
FROM table_name
GROUP BY author
ORDER BY COUNT(author) DESC LIMIT 10;

#2