首页 / PHP / php – 两个看似相同的查询在使用count(分页)时返回不同的结果

php – 两个看似相同的查询在使用count(分页)时返回不同的结果

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了php – 两个看似相同的查询在使用count(分页)时返回不同的结果，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3000字，纯文字阅读大概需要5分钟。

内容图文

由于分页原因,我正在向我的数据库运行两个查询.因此,每个查询几乎相同.我的COUNT(*)查询未返回非计数查询的结果数.我很困惑为什么会这样.查询如下.

SELECT p.host_id, p.rating_support, p.rating_tech, MAX(p.rating_overall) AS rating_overall, p.publish_rating, h.name, prices.price, prices.term_duration
FROM plans p
INNER JOIN hosts AS h ON h.id = p.host_id
INNER JOIN (SELECT plan_id, price, term_duration FROM prices WHERE price > 0 AND price < 50 AND term_duration = 1) prices ON prices.plan_id = p.id
WHERE p.published = 1 AND h.published = 1
GROUP BY p.host_id
ORDER BY rating_overall desc LIMIT 0, 12

SELECT COUNT(*) AS count
FROM plans p
INNER JOIN hosts AS h ON h.id = p.host_id
INNER JOIN (SELECT plan_id, price, term_duration FROM prices WHERE price > 0 AND price < 50 AND term_duration = 1) prices ON prices.plan_id = p.id
WHERE p.published = 1 AND h.published = 1
GROUP BY p.host_id

我不是MySQL的专家.除了计数不提供正确数量的结果之外,非计数查询也能完美地工作.

对这个问题的任何启示都会很棒.

解决方法:

在Dems的评论(hunt down and upvote him某处:)的帮助下,我创建了这个查询.请注意,我删除了子查询,因为它似乎没必要：

SELECT
  COUNT( DISTINCT p.host_id )
FROM       plans p
INNER JOIN hosts h ON h.id = p.host_id
INNER JOIN prices  ON prices.plan_id = p.id
                  AND prices.price > 0
                  AND prices.price < 50
                  AND prices.term_duration = 1
WHERE p.published = 1
  AND h.published = 1

我的原始答案：

要获取总行数,必须将GROUP BY查询包装到外部SELECT：

SELECT COUNT(*)
FROM (
  SELECT NULL -- we are just counting, so we need no actual data -> a bit faster
  FROM       plans p
  INNER JOIN hosts h ON h.id = p.host_id
  INNER JOIN prices  ON prices.plan_id = p.id
                    AND prices.price > 0
                    AND prices.price < 50
                    AND prices.term_duration = 1
  WHERE p.published = 1
    AND h.published = 1
  GROUP BY p.host_id
) AS all_rows_without_data

或者您可以使用SQL_CALC_FOUND_ROWS FOUND_ROWS()

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

A SELECT statement may include a LIMIT clause to restrict the number
of rows the server returns to the client. In some cases, it is
desirable to know how many rows the statement would have returned
without the LIMIT, but without running the statement again. To obtain
this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT
statement, and then invoke FOUND_ROWS() afterward:

首先,只需选择所需的行,但添加SQL_CALC_FOUND_ROWS：

SELECT SQL_CALC_FOUND_ROWS
  p.host_id, p.rating_support, p.rating_tech,
  MAX(p.rating_overall) AS rating_overall,
  p.publish_rating, h.name, prices.price, prices.term_duration
FROM       plans p
INNER JOIN hosts AS h ON h.id = p.host_id
INNER JOIN prices  ON prices.plan_id = p.id
                  AND prices.price > 0
                  AND prices.price < 50
                  AND prices.term_duration = 1
WHERE p.published = 1 AND h.published = 1
GROUP BY p.host_id
ORDER BY rating_overall desc
LIMIT 0, 12;

其次,如果第一个查询中没有LIMIT语句,则获取将返回的行数：

SELECT FOUND_ROWS();

更新：SQL_CALC_FOUND_ROWS FOUND_ROWS()看起来不太可靠,因为未知原因总是回零(不仅仅是我：FOUND_ROWS() keeps returning 0)：

http://sqlfiddle.com/#!2/7304d/8

内容总结

以上是互联网集市为您收集整理的php – 两个看似相同的查询在使用count(分页)时返回不同的结果全部内容，希望文章能够帮你解决php – 两个看似相同的查询在使用count(分页)时返回不同的结果所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/720635.html

来源：【匿名】