mysql - 我如何: ORDER BY COUNT(column1) from the result of: SELECT DISTINCT column1,栏2

Question

因为标题可能很糟糕，而且我对 SQL 很弱，所以我会尝试阐明我的目标:

如果:

SELECT DISTINCT host, author FROM t;

看起来像:

foo.com | John
bar.com | Bob
bar.com | Alice

如何获得如下结果:

foo.com | 1
bar.com | 2

如果这还不够清楚，请告诉我，我会更新或回复评论。

Answer 1

您需要分组依据，而不是选择不同:

SELECT host, count(*)
FROM t
GROUP BY host
ORDER BY count(*);

如果您可能有重复的主持人/作者组合，那么您可能需要count(不同作者):

SELECT host, count(distinct author)
FROM t
GROUP BY host
ORDER BY count(distinct author);