计算帖子数量使用每个标签

Question

因此，我正在研究一个自定义博客系统，每篇文章中，我都会显示与该帖子相关的标签列表。我想在每个标签中旁边的每个标签旁边都有一个数量的数量，即其他每个标签使用了多少帖子，这就是我在弄清楚的问题。

在这里，我有一张帖子，标签，然后是post_tags的3个表，用于分配哪些帖子具有标签。

mysql> DESC post; 
+----------+--------------+------+-----+---------+----------------+ 
| Field    | Type         | Null | Key | Default | Extra          | 
+----------+--------------+------+-----+---------+----------------+ 
| id       | mediumint(9) | NO   | PRI | NULL    | auto_increment | 
| contents | longtext     | NO   |     | NULL    |                | 
+----------+--------------+------+-----+---------+----------------+

mysql> DESC tags; 
+-------+--------------+------+-----+---------+----------------+ 
| Field | Type         | Null | Key | Default | Extra          | 
+-------+--------------+------+-----+---------+----------------+ 
| id    | mediumint(9) | NO   | PRI | NULL    | auto_increment | 
| tag   | varchar(255) | NO   |     | NULL    |                | 
+-------+--------------+------+-----+---------+----------------+ 

mysql> DESC post_tags; 
+---------+--------------+------+-----+---------+----------------+ 
| Field   | Type         | Null | Key | Default | Extra          | 
+---------+--------------+------+-----+---------+----------------+ 
| id      | mediumint(9) | NO   | PRI | NULL    | auto_increment | 
| post_id | mediumint(9) | NO   |     | NULL    |                | 
| tag_id  | mediumint(9) | NO   |     | NULL    |                | 
+---------+--------------+------+-----+---------+----------------+

这是我用来提取帖子标签列表的代码

$stmt = $con->prepare('SELECT * FROM tags LEFT JOIN post_tags ON post_tags.tag_id = tags.id JOIN post ON post.id = post_tags.post_id WHERE post.id = ?');
$stmt->bind_param('i', $_GET['id']);
$stmt->execute();
$tags = $stmt->get_result();

，以及如何显示它们

<?php while ($tag = $tags->fetch_assoc()) : ?>
<span class="tags"><a href="tags.php?<?= $tag['tag'] ?>" class="tag tag-35762 "><span class="name"><?= $tag['tag'] ?></span><span class="count">1</span></a>
<? endwhile ?>

Answer 1

这样的东西？

SELECT t.*, IF(post_count is null,0,post_count) as post_count
FROM tags AS t 
LEFT JOIN(SELECT COUNT(*) as post_count,tag_id FROM post_tags GROUP BY tag_id) as pcount 
     ON pcount.tag_id = t.id

或者

SELECT t.*, count(pt.id) as post_count
FROM tags AS t 
LEFT JOIN post_tags pt ON pt.tag_id = t.id
GROUP BY t.id