mySQL 右外连接

发布于 2024-11-27 03:42:22 字数 417 浏览 2 评论 0原文

我对 SQL 很陌生，正在尝试构建一个查询：

$result = mysql_query("SELECT COUNT(*) AS s_count FROM solution RIGHT OUTER JOIN 
ON offer.oid = solution.oid ". "WHERE offer.is_solved = 0  ORDER BY offer.creation_time 
DESC LIMIT $interval_begin, $interval_end");

该查询应该是像商店这样的环境中的实时报价机：它需要计算所有尚未解决的报价，然后在一定的时间间隔内列出它们以反映特定的页面列表的。例如，如果每个年龄有 25 个优惠，第 2 页将是 26-50。

谁能看出为什么输出为0？所有列/表都存在并且其中确实有测试值。

原文

Im quite new to SQL and am trying to construct a query:

$result = mysql_query("SELECT COUNT(*) AS s_count FROM solution RIGHT OUTER JOIN 
ON offer.oid = solution.oid ". "WHERE offer.is_solved = 0  ORDER BY offer.creation_time 
DESC LIMIT $interval_begin, $interval_end");

The query is supposed to be a liveticker in a shop like environnment: it needs to count all offers that havent been solved and then list them for a certain interval to reflect a certain page of the listing.
eg if there are 25 offers per age, page 2 would be 26-50.

Can anyone spot why the output is 0? All the columns/tables exist and do have test values in them.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

贪了杯 2024-12-04 03:42:22

查询不应该像这样

mysql_query("SELECT COUNT(*) AS s_count FROM solution RIGHT OUTER JOIN  offer ON offer.oid = solution.oid WHERE offer.is_solved = 0  ORDER BY offer.creation_time  DESC LIMIT".$interval_begin.",".$interval_end);

Shouldn't the query be like

mysql_query("SELECT COUNT(*) AS s_count FROM solution RIGHT OUTER JOIN  offer ON offer.oid = solution.oid WHERE offer.is_solved = 0  ORDER BY offer.creation_time  DESC LIMIT".$interval_begin.",".$interval_end);

回复收藏 0 原文

乙白 2024-12-04 03:42:22

调查您的问题和查询，我认为您的查询始终返回 0 的最明显原因可能是：

表 offer 没有 is_solved = 0 对于其任何记录。因此，WHERE 条件 offer.is_solved = 0 没有匹配的条目。您能否确认一下您的数据？
表offer没有数据，因此RIGHT JOIN不会产生任何结果。但正如您在对 Rahul 的回答的评论之一中提到的，offer 中有 3000 个条目，我想这个原因不太可能。

啊哈，可能还有另一个原因：您确定由 $interval_begin 和 $interval_end 标记的限制和偏移量设置正确吗？也许如果它们都设置为 0，则查询将始终返回 0。您可以尝试对查询进行“回显/打印”，看看它到底是什么样子吗？

因为我不了解您的架构，这只是一个建议，我认为如果您有 offer.is_solved 列，则不需要与 solution 进行 RIGHT JOIN 。下面的查询应该同样可以正常工作，不是吗？

SELECT COUNT(*) AS `s_count`
FROM `offer`
WHERE `offer`.`is_solved` = 0
ORDER BY `offer`.`creation_time`
DESC LIMIT $interval_begin, $interval_end;

希望有帮助！

looking into your question and your query, I think the most obvious reason(s) why your query always return 0 could be:

The table offer does not have is_solved = 0 for any of its records. As a result, the WHERE condition offer.is_solved = 0 has no matching entries. Can you please confirm this for your data?
The table offer has no data and thus the RIGHT JOIN yields no results. But as you have mentioned in one of your comments to Rahul's answer, there are 3000 entries in offer, I guess this reason is unlikely.

Aha, there could be another reason too: are you sure the limit and offset, marked by $interval_begin and $interval_end are correctly set? Perhaps if they are both set to 0, the query will always return 0. Can you try doing an "echo/print" of your query and see how does it exactly look?

Because I have no understanding of your schema, this is only a suggestion, I think that if you have an offer.is_solved column, you do not need a RIGHT JOIN with solution. The following query should work equally fine, no?

SELECT COUNT(*) AS `s_count`
FROM `offer`
WHERE `offer`.`is_solved` = 0
ORDER BY `offer`.`creation_time`
DESC LIMIT $interval_begin, $interval_end;

Hope it helps!

回复收藏 0 原文

走过海棠暮 2024-12-04 03:42:22

您的查询中需要解决方案表的目的是什么？从你的文字看来

mysql_query("SELECT COUNT(*) FROM offer offer WHERE offer.is_solved = 0  ORDER BY offer.creation_time  DESC LIMIT".$interval_begin.",".$interval_end);

就足够了。

“我们需要知道的是每个“提议”的解决方案的确切数量，而不仅仅是是否有任何解决方案。” - 在这种情况下，你最初的 SQL 是错误的，它只是计算订单。你需要类似的东西

mysql_query("SELECT offer.oid, COUNT(solution.oid) FROM offer offer LEFT JOIN ON offer.oid = solution.oid WHERE offer.is_solved = 0 GROUP BY offer.oid ORDER BY offer.creation_time  DESC LIMIT".$interval_begin.",".$interval_end);

What for do you need solution table in your query? From your text it looks like

mysql_query("SELECT COUNT(*) FROM offer offer WHERE offer.is_solved = 0  ORDER BY offer.creation_time  DESC LIMIT".$interval_begin.",".$interval_end);

would be enough.

"What we need to know is the exact number of solutions for each "offer", not just whether there is any solution at all." - In such case your initial SQL was wrong, it just counted orders. You need something like

mysql_query("SELECT offer.oid, COUNT(solution.oid) FROM offer offer LEFT JOIN ON offer.oid = solution.oid WHERE offer.is_solved = 0 GROUP BY offer.oid ORDER BY offer.creation_time  DESC LIMIT".$interval_begin.",".$interval_end);

回复收藏 0 原文

~没有更多了~