我有一个具有关联author_id的帖子列表,并且我选择每个作者至少有一个发布过的帖子列表,如下所示:
SELECT DISTINCT author_id
FROM posts
WHERE status = "live" AND author_id NOT IN(0,1)
我想按照创建的帖子数量从上到下对列表进行排序,但是我不知道如何计算它们并按该计数对列表进行排序。
帮忙吗?
您需要使用GROUP BY来获取帖子数
SELECT author_id, COUNT(*) as posts_count
FROM posts
WHERE status = "live" AND author_id NOT IN(0,1)
GROUP BY author_id
ORDER BY posts_count DESC;