mysql - 从 Xoops 数据库中计算并插入评论

Question

我正在尝试将 Xoops 帖子转换为 Wordpress。作为其中的一部分，我想获得某个主题的评论数。帖子和回复在同一个“topic_id”上。如何计算它们并将其发布到新专栏中？

数据库当前状态

topic_id | subject             |comment_count|
+________+_____________________+_____________+
1    | welcome             | 
1    | Re: welcome         |
2    | hello world         |
2    | Re: hello world     |
2    | Re: hello world     |
3    | hello friends       |

从这里我想将(topic_id 的计数 - 1)作为重播次数(评论计数)。指导我在MYSQL中查询

我想将输出放在同一个表中。 (评论数)

数据库预期输出

   | topic_id | subject             |comment_count|
   +________+_____________________+_____________+
   | 1   | welcome             | 1 
   | 1   | Re: welcome         | 1
   | 2   | hello world         | 2
   | 2   | Re: hello world     | 2
   | 2   | Re: hello world     | 2
   | 3   | hello friends       | 0

Answer 1

    select *,t2.comment_count  from table t1 
     join
     (
       select count(*),CONCAT('Re', ' ', subject) 
        as replay,topic_id as comment_count 
        from table 
        where suject=replay group by topic_id
     ) as t2 on t1.topic_id=t2.topic_id