COUNT在具有多个JOINS和GROUP BY CLAUSE的查询中 [英] COUNT in a query with multiple JOINS and a GROUP BY CLAUSE

问题描述

我正在处理包含3个表格的数据库：

I am working on a database that contains 3 tables:

1. 公司列表

1. A list of companies
2. A table of the products they sell
3. A table of prices they offered on each date

>我在我的PHP中进行这样的查询，以生成在某个特定日期在某个产品类型上提供最低价格的公司的列表。

I'm doing a query like this in my php to generate a list of the companies offering the lowest prices on a certain product type on a certain date.

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中实现一个寻呼机，我需要知道当天有多少公司提供该产品总共。 LIMIT意味着我只看到前几个...

This gets me the data I need, but in order to implement a pager in PHP I need to know how many companies offering that product on that day there are in total. The LIMIT means that I only see the first few...

我尝试将SELECT子句更改为SELECT COUNT（a.id）或SELECT COUNT（DISTINCT（a。 id）），但没有一个似乎给了我想要的东西。我试图删除GROUP BY和ORDER BY在我的计数查询，但是没有工作。任何想法？

I tried changing the SELECT clause to SELECT COUNT(a.id) or SELECT COUNT(DISTINCT(a.id)) but neither of those seem to give me what I want. I tried removing the GROUP BY and ORDER BY in my count query, but that didn't work either. Any ideas?

推荐答案

看起来像你应该 GROUP BY a.id，c.id - a.id 分组只表示通常有几个 c.id s每 a.id ，你只是得到一个随机是其中之一。这似乎是一个基本正确的问题。一旦你修正了，一个初始 SELECT COUNT（*）FROM 等等，然后肯定会给你下面的查询将返回的行数，所以你可以准备你的寻呼机因此。

Looks to me like you should GROUP BY a.id, c.id -- grouping by a.id only means you'll typically have several c.ids per a.id, and you're just getting a "random-ish" one of them. This seems like a question of basic correctness. Once you have fixed that, an initial SELECT COUNT(*) FROM etc etc should then definitely give you the number of rows the following query will return, so you can prepare your pager accordingly.

这篇关于COUNT在具有多个JOINS和GROUP BY CLAUSE的查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！