SQL -如何使用另一个表中的计数进行排序

Question

1.博客

blogger_id
1 
2
3

2. Posts

post_from_blogger_id
1 
1
1
2
2
3

正如你所看到的，博主№1发布的信息比其他人多，博主№3发布的信息更少。问题是如何构建一个查询来选择所有博客作者，并根据他们的帖子数量对他们进行排序？

Answer 1

 SELECT bloggers.*, COUNT(post_id) AS post_count
    FROM bloggers LEFT JOIN blogger_posts 
    ON bloggers.blogger_id = blogger_posts.blogger_id
    GROUP BY bloggers.blogger_id
    ORDER BY post_count

(注意: MySQL有特殊的语法，允许您分组而不聚合所有值，它正是为这种情况而设计的)。

Answer 2

使用子查询。

select * from (
    select post_from_blogger_id, count(1) N from Posts
    group by post_from_blogger_id) t
order by N desc

Answer 3

试试这个：

SELECT B.blogger_id,
       B.blogger_name,
       IFNULL(COUNT(P.post_from_blogger_id ),0) AS NumPosts 
From Blogger AS B
LEFT JOIN Posts AS P ON P.post_from_blogger_id = B.blogger_id
GROUP BY B.blogger_id, B.blogger_name
ORDER BY COUNT(P.post_from_blogger_id ) DESC

这将连接这两个表，并计算Posts表中的条目数量。如果没有，则计数为0 (IFNULL)。