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

问题描述

1.博客

blogger_id
1 
2
3

2.帖子

post_from_blogger_id
1 
1
1
2
2
3

如您所见，博主№1的发布人数比其他博主更多，博主№3的发布人数少.问题是如何建立一个查询来选择所有博客并按帖子数量对它们进行排序?

As you can see blogger №1 posted more than the others and blogger №3 less. The question ishow to build a query that selects all bloggers and sorts them by the number of their posts?

推荐答案

 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具有特殊的语法，可让您通过GROUP BY而不汇总所有值，正是针对这种情况).

(Note: MySQL has special syntax that lets you GROUP BY without aggregating all values, it's intended for exactly this situation).

这篇关于SQL-如何使用另一个表中的计数进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！