MySQL COUNT() 特定条件下的帖子总数?
我一直在试图弄清楚我做错了什么,让我在直接讨论问题之前解释一下我的 MySQL 结构(以便你更好地理解)。
我有一个简单的 PHP 论坛,并且在两个表(用于帖子和主题)中都有一个名为“已删除”的列,如果它等于 0,则意味着它已显示(被认为未删除/存在),或者如果它等于 1,则它被隐藏(被认为已删除/不存在) - 布尔/精益。
现在,我正在讨论的“具体标准”...我想使用其 id (forum_id) 获取特定论坛内的帖子总数,确保它只计算未删除的帖子 (deleted = 0)并且它们的父主题也不会被删除(已删除 = 0)。
列/表名称是不言自明的(如果需要,请参阅下面我的努力)。
我已经尝试过以下操作(使用“简单”连接):
SELECT COUNT(t1.post_id)
FROM forum_posts AS t1, forum_topics AS t2
WHERE t1.forum_id = '{$forum_id}'
AND t1.deleted = 0
AND t1.topic_id = t2.topic_id
AND t2.deleted = 0
LIMIT 1
我也尝试过这个(使用子查询):
SELECT COUNT(t1.post_id)
FROM forum_posts AS t1
WHERE t1.forum_id = '{$forum_id}'
AND t1.deleted = 0
AND (SELECT deleted
FROM forum_topics
WHERE topic_id = t1.topic_id) = 0
LIMIT 1
但是两者都不符合特定要求标准。
感谢所有帮助! :)
I've been losing my hair trying to figure out what I'm doing wrong, let me explain abit about my MySQL structure (so you get a better understanding) before I go straight to the question.
I have a simple PHP forum and I have a column in both tables (for posts and topics) named 'deleted' if it equals 0 that means its displayed (considered not deleted/exists) or if it equals 1 it hidden (considered deleted/doesn't exist) - bool/lean.
Now, the 'specific criteria' I'm on about...I'm wanting to get a total post count within a specific forum using its id (forum_id), ensuring it only counts posts which are not deleted (deleted = 0) and their parent topics are not deleted either (deleted = 0).
The column/table names are self explanatory (see my efforts below for them - if needed).
I've tried the following (using a 'simple' JOIN):
SELECT COUNT(t1.post_id)
FROM forum_posts AS t1, forum_topics AS t2
WHERE t1.forum_id = '{$forum_id}'
AND t1.deleted = 0
AND t1.topic_id = t2.topic_id
AND t2.deleted = 0
LIMIT 1
I've also tried this (using a Subquery):
SELECT COUNT(t1.post_id)
FROM forum_posts AS t1
WHERE t1.forum_id = '{$forum_id}'
AND t1.deleted = 0
AND (SELECT deleted
FROM forum_topics
WHERE topic_id = t1.topic_id) = 0
LIMIT 1
But both don't comply with the specific criteria.
Appreciate all help! :)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这将使用 topic_id 连接两个表,并且仅返回满足已删除 = 0 和所需论坛 id 的两个表的条件的结果。
This will join the two tables using the topic_id, and only return results that meet the criteria of both tables having deleted = 0 and the desired forum id.
我不确定我是否理解您的问题,但您可以从以下查询开始,并对其进行微调以执行您想要的操作:
我向查询添加了注释以帮助您解决问题。
I am not sure I understand your question, but you can start with the following query and fine tune it to do what you want:
I added comments to the query to help you out.