我有两个表,1)hashtags表和2)hashtags_relation表。
第一个列出了标签,第二个将标签与post关联起来。
表标签

 id_hashtag | hashtag |
  3         #love
  4         #monday
  5         #yesterday

**TABLE HASHTAGS RELATIONS**


id_hashtag | id_post |
3           1234
3           1235
3           1236
4           1541
4           1543
5           1720
5           1721
5           1722
5           1723
5           1724

预期产量
 #yesterday - 5 posts
 #love - 3 posts
 #monday - 2 posts

SQL查询(MYSQL)
SELECT hs.*, ht.count(*) as count ?
FROM hashtags hs
LEFT JOIN hashtag_relation hr ON hs.id_hashtag=hr.id_hashtag
WHERE 1
ORDER BY (ht.number of posts?)
DESC
LIMIT 10

问题
如何获取每个标签的帖子数?我尝试使用ht.count(*)

最佳答案

您需要使用GROUP BY

SELECT hs.hashtag, count(hr.id_hashtag) as cnt
FROM hashtags hs
LEFT JOIN hashtag_relation hr ON hs.id_hashtag=hr.id_hashtag
GROUP BY hs.hashtag
ORDER BY cnt DESC LIMIT 10;

10-05 20:32
查看更多