php - MySQL获取另一个表中的所有行的计数，其中值等于当前表中的列

Question

我正在用 PHP 编写一个论坛，因此我需要计算论坛中的线程数量。我使用以下查询来获取所有论坛及其各自的类别:

SELECT f.id
     , f.name
     , f.description
     , c.id category_id
     , c.name category_name
     , c.description category_description 
  FROM forum_forums f
  JOIN forum_forums_categories fc
    ON f.id = fc.forum_id   
  JOIN forum_categories c
    ON fc.category_id = c.id;

它完成了工作，然后我就可以将所有内容分组。接下来我想做的是将某个论坛中的线程数量添加到结果中的每一行，但我不确定如何做到这一点。

我有以下表格:forum_forums、forum_threads、forum_categories。此外，线程可以属于多个论坛(我有一个 forums_threads_forums 表，它将特定的 thread_id 绑定(bind)到 forum_id)。

所以我的猜测是我需要在原始命令中的某处添加计数。此计数需要计算 forum_threads_forums 表中的行数，其中 forum_id 等于它添加到结果中的当前论坛的行数。

<小时/>

为了让事情变得更简单，这是我想要实现的目标的示例(简化):表:forum_forums

id  name
1   forum1
2   forum2
3   forum3

表:forum_threads

id  title
1   thread1
2   thread2

表:forum_threads_forums

thread_id  forum_id
1          1
1          2
2          1
2          3

然后我希望返回查询(除其他外):

forum_forums.id  forum_forums.name  forum.threads
1                forum1             2
2                forum2             1
3                forum3             1

如果有人能插入我朝正确的方向前进，那就太好了。

编辑:

我想我可能需要一个子查询，例如 SELECT COUNT(thread_id) AS thread_count FROM forum_threads_forums WHERE forum_id=:forum_id 但我不确定将其放在原始查询中的何处

答案:

为了供将来引用，这是我现在使用的工作命令:

SELECT
    forum_forums.id,
    forum_forums.name,
    forum_forums.description,
    COUNT(forum_threads_forums.thread_id) AS thread_count,
    forum_categories.id AS category_id,
    forum_categories.name AS category_name,
    forum_categories.description AS category_description 
FROM
    forum_forums 
LEFT OUTER JOIN
    forum_threads_forums 
ON
    forum_forums.id=forum_threads_forums.forum_id 
INNER JOIN
    forum_forums_categories 
ON
    forum_forums.id=forum_forums_categories.forum_id 
INNER JOIN
    forum_categories 
ON
    forum_forums_categories.category_id=forum_categories.id 
GROUP BY
    forum_forums.id

Answer 1

我怀疑是否有关于聚合函数的入门级教程未能涵盖这一点，但无论如何......

 DROP TABLE IF EXISTS forums;
 CREATE TABLE forums
 (forum_id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,forum_name VARCHAR(12) NOT NULL UNIQUE
 );


 INSERT INTO forums VALUES
 (1   ,'forum1'),(2,'forum2'),(3,'forum3');

 DROP TABLE IF EXISTS threads;
 CREATE TABLE threads
 (thread_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,title VARCHAR(12) NOT NULL
 );

 INSERT INTO threads VALUES
 (1   ,'thread1'),
 (2   ,'thread2');

 DROP TABLE IF EXISTS threads_forums;
 CREATE TABLE threads_forums
 (thread_id  INT NOT NULL
 ,forum_id INT NOT NULL
 ,PRIMARY KEY(thread_id,forum_id)
 );

 INSERT INTO threads_forums VALUES
 (1          ,1),
 (1          ,2),
 (2          ,1),
 (2          ,3);

 SELECT f.*
      , COUNT(t.thread_id) threads 
   FROM forums f 
   JOIN threads_forums tf 
     ON tf.forum_id = f.forum_id 
   JOIN threads t 
     ON t.thread_id = tf.thread_id 
  GROUP  
     BY forum_id;
 +----------+------------+---------+
 | forum_id | forum_name | threads |
 +----------+------------+---------+
 |        1 | forum1     |       2 |
 |        2 | forum2     |       1 |
 |        3 | forum3     |       1 |
 +----------+------------+---------+

请注意，此解决方案不会显示没有主题的论坛。为此，您需要使用 LEFT [OUTER] JOIN 代替