mysql - 无法找出关联两个表的 SQL 查询

Question

好的，我有两张 table 。

表“主题”

tid | posts 
------------
1       4
3       2

表“帖子”

topic_id |  post
-----------------
1      xxxx
3      aaaa
1      dddd
1      ddsdss
3      rreer
1      gsdsd

我的抽象查询是这样的:

select counter(*) as c 
from posts as p, topic as t 
where p.tid = t.topicid 
  and c != t.posts

这应该返回与“主题”表 tid 匹配但与表“主题”帖子计数不匹配的所有“帖子”表行的计数器。

如果发现不同，我还需要使用计数器(*)值更新主题的“帖子”列。

update topic set posts = (SELECT COUNT(*) from posts WHERE posts.topicid = topic.tid and topic.posts <> (SELECT COUNT(*) from posts WHERE posts.topicid = topic.tid))

我尝试了很多方法来让它工作，但无法弄清楚。如果有人可以解释此案例的查询，我将不胜感激？

<小时/>

其实我想做的是这个。如果“帖子”表的帖子计数与“主题”表帖子计数器不匹配，我想更新表之间所有匹配主题 ID 的主题帖子计数器。你明白我的意思吗？我尝试使用您的查询，但它以其他方式为我提供“主题”表行，这不是我想要的。

UPDATE topics SET posts = (
  SELECT COUNT(*) 
  FROM posts 
  WHERE topicid = (
    SELECT tid 
    FROM topics 
    WHERE topics.tid = topicid
 ) 
 GROUP BY topicid HAVING COUNT(*) <> (
   SELECT posts 
   FROM topics WHERE topics.tid = topicid
 )
) **WHERE tid = (
  SELECT topicid 
  FROM posts 
  WHERE topicid = (
    SELECT tid 
    FROM topics 
    WHERE topics.tid = topicid
  ) GROUP BY topicid HAVING COUNT(*) <> (
      SELECT posts FROM topics WHERE topics.tid = topicid
  )
)**

Answer 1

下面将为您提供主题和计数

如果您想找到“错误”的主题，请将其加入计数表

SELECT *
FROM topic
LEFT JOIN (
  SELECT topic_id, count(*) as t_count
  FROM posts
  GROUP BY topic_id
) sub on topic.tid  = sub.topic_id

然后要查看不同的内容，您可以添加一个位置

WHERE topic.posts <> sub.t_count