具有多个联接和GROUP子句的查询中的计数

Question

我正在开发一个包含3个表的数据库：

1. 公司名单
2. 他们出售的产品表
3. 他们在每个日期提出的价目表

我正在我的php中执行这样的查询，以生成在某一特定日期提供某一特定产品类型的最低价格的公司列表。

SELECT
  a.name AS company,
  c.id,
  MIN(c.price) AS apy
FROM `companies` a
JOIN `company_products` b ON b.company_id = a.id
JOIN `product_prices` c ON c.product_id = b.id
WHERE
  b.type = "%s"
  AND c.date = "%s"
GROUP BY a.id
ORDER BY c.price ASC
LIMIT %d, %d

这得到了我需要的数据，但是为了在PHP中实现一个寻呼机，我需要知道那天有多少家公司提供这个产品。限制意味着我只看到最初的几个..。

我尝试将SELECT子句更改为SELECT COUNT(a.id)或SELECT (DISTINCT(a.id))，但这两个选项似乎都不能满足我的需要。我尝试在计数查询中删除组BY和ORDER，但这也不起作用。有什么想法吗？

Answer 1

在我看来，您应该使用GROUP BY a.id, c.id --按a.id分组只意味着每个a.id通常有几个c.id，而您只是得到其中的一个“随机”的。这似乎是一个基本正确性的问题。一旦您修复了这个问题，一个初始的SELECT COUNT(*) FROM等就应该明确地给出以下查询返回的行数，这样您就可以相应地准备寻呼机了。

Answer 2

本网站建议MySQL在这方面有一个特殊的诀窍，至少在第4版时是这样的：

幸运的是，由于MySQL 4.0.0，您可以在查询中使用SQL_CALC_FOUND_ROWS选项，它将告诉MySQL计算行总数而忽略限制子句。您仍然需要执行第二个查询才能检索行数，但它是一个简单的查询，而不像检索数据的查询那么复杂。

用法很简单。在主查询中，您需要在SELECT之后添加SQL_CALC_FOUND_ROWS选项，而在第二个查询中，您需要使用FOUND_ROWS()函数来获得总行数。查询将如下所示：

SELECT SQL_CALC_FOUND_ROWS name, email 
FROM users 
WHERE name LIKE 'a%' 
LIMIT 10;

SELECT FOUND_ROWS();

唯一的限制是必须在第一个查询之后立即调用第二个查询，因为SQL_CALC_FOUND_ROWS不会在任何地方保存行数。