COUNT в запросе с несколькими JOINS и GROUP BY CLAUSE

Я работаю над базой данных, которая содержит 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 мне нужно знать, сколько всего компаний, предлагающих этот продукт в этот день, существует. LIMIT означает, что я вижу только первые несколько...

Я попытался изменить предложение SELECT на SELECT COUNT(a.id) или SELECT COUNT(DISTINCT(a.id)) , но ни один из них не дает мне того, что я хочу. Я попытался удалить GROUP BY и ORDER BY в моем запросе на подсчет, но это тоже не сработало. Есть идеи?


mysql sql join count
person mishac    schedule 10.05.2009    source источник
comment
Остерегайтесь возможностей для атаки SQL-инъекцией, предоставляемых двумя строками %s.   -  person Jonathan Leffler    schedule 11.05.2009
comment
Строки генерируются PHP, а не пользовательским вводом, поэтому я думаю, что все в порядке.   -  person mishac    schedule 11.05.2009

Ответы (2)


arrow_upward
3
arrow_downward

Мне кажется, что вы должны GROUP BY a.id, c.id -- группировка по a.id означает только то, что у вас обычно будет несколько c.id на a.id, и вы просто получаете "случайный" один из них. Это похоже на вопрос базовой правильности. После того, как вы исправите это, начальный SELECT COUNT(*) FROM и т. д. и т. д. должен определенно дать вам количество строк, которые вернет следующий запрос, поэтому вы можете соответствующим образом подготовить свой пейджер.

person Alex Martelli    schedule 10.05.2009
comment
Разве MIN (c.price) не заботится о том, какой c.id выбран? - person mishac; 11.05.2009
comment
Нет. Все в списке выбора, не являющееся агрегатом, должно быть указано в предложении GROUP BY. Некоторые СУБД ослабляют это ограничение, но большинство — нет. - person Jonathan Leffler; 11.05.2009
comment
Хорошее предложение. Тем не менее, предложение MySQL LIMIT будет увеличивать общее количество (*). - person Andomar; 11.05.2009
comment
Если я добавлю c.id в предложение group by, это означает, что в списке отображаются несколько элементов с одной и той же компанией. Мне нужно, чтобы каждая компания появилась только один раз. Есть идеи, где я ошибся? - person mishac; 11.05.2009
comment
Почему вы пытаетесь отобразить c.id? На компанию в день приходится несколько c.id. Опустите c.id из предложения group by и select. - person Andomar; 11.05.2009
comment
C.id существует, потому что таблица HTML, которую я создаю, будет иметь парящий элемент AJAX, который показывает другие (не относящиеся к этому запросу) детали из таблицы цен, и мне нужно получить этот идентификатор, чтобы убедиться, что правильная строка показано там. - person mishac; 11.05.2009
comment
Использовать для этого идентификатор компании? Так как есть только одна строка на компанию. Ваше оригинальное решение тоже сработает: случайный c.id будет уникальным для каждой строки, независимо от того, какой c.id вы получили. Полагаться на случайность необычно, поэтому, если вы идете по этому пути, разместите комментарий, объясняющий это. - person Andomar; 11.05.2009
comment
Я не могу использовать идентификатор компании, потому что у каждой компании есть несколько цен в день, и каждая цена имеет определенные условия, которые также хранятся в таблице цен. Мне нужно иметь точный c.id для строки с отображаемой ценой. - person mishac; 11.05.2009

arrow_upward
3
arrow_downward

Этот сайт предполагает, что у MySQL есть специальный трюк для этого, по крайней мере, начиная с версии 4:

К счастью, начиная с MySQL 4.0.0, вы можете использовать параметр SQL_CALC_FOUND_ROWS в своем запросе, который сообщит MySQL о подсчете общего количества строк без учета предложения LIMIT. Вам все еще нужно выполнить второй запрос, чтобы получить количество строк, но это простой запрос и не такой сложный, как ваш запрос, который извлек данные.

Использование довольно простое. В основной запрос вам нужно добавить параметр SQL_CALC_FOUND_ROWS сразу после SELECT, а во втором запросе вам нужно использовать функцию FOUND_ROWS(), чтобы получить общее количество строк. Запросы будут выглядеть так:

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

SELECT FOUND_ROWS();

Единственное ограничение состоит в том, что вы должны вызывать второй запрос сразу после первого, потому что SQL_CALC_FOUND_ROWS нигде не сохраняет количество строк.

person Andomar    schedule 10.05.2009