Получите SUM в GROUP BY с помощью JOIN, используя MySQL

У меня есть две таблицы в MySQL 5.1.38.

products
+----+------------+-------+------------+
| id | name       | price | department |
+----+------------+-------+------------+
|  1 | Fire Truck | 15.00 | Toys       |
|  2 | Bike       | 75.00 | Toys       |
|  3 | T-Shirt    | 18.00 | Clothes    |
|  4 | Skirt      | 18.00 | Clothes    |
|  5 | Pants      | 22.00 | Clothes    |
+----+------------+-------+------------+

ratings
+------------+--------+
| product_id | rating |
+------------+--------+
|          1 |      5 |
|          2 |      5 |
|          2 |      3 |
|          2 |      5 |
|          3 |      5 |
|          4 |      5 |
|          5 |      4 |
+------------+--------+

Моя цель - получить общую стоимость всех продуктов, имеющих рейтинг 5 звезд в каждом отделе. Что-то вроде этого.

+------------+-------------+
| department | total_price |
+------------+-------------+
| Clothes    | 36.00       |  /* T-Shirt and Skirt */
| Toys       | 90.00       |  /* Fire Truck and Bike */
+------------+-------------+

Я хотел бы сделать это без подзапроса, если смогу. Сначала я попробовал соединение с помощью sum().

select department, sum(price) from products
join ratings on product_id=products.id
where rating=5 group by department;
+------------+------------+
| department | sum(price) |
+------------+------------+
| Clothes    |      36.00 |
| Toys       |     165.00 |
+------------+------------+

Как вы можете видеть, цена на отдел игрушек неверна, потому что есть два 5-звездочных рейтинга для байка и, следовательно, считая эту цену дважды за счет соединения.

Затем я попытался добавить к сумме сумму.

select department, sum(distinct price) from products
join ratings on product_id=products.id where rating=5
group by department;
+------------+---------------------+
| department | sum(distinct price) |
+------------+---------------------+
| Clothes    |               18.00 |
| Toys       |               90.00 |
+------------+---------------------+

Но тогда отдел одежды отключен, потому что два продукта имеют одинаковую цену.

В настоящее время моя работа включает в себя получение чего-то уникального продукта (id) и использование этого, чтобы сделать цену уникальной.

select department, sum(distinct price + id * 100000) - sum(id * 100000) as total_price
from products join ratings on product_id=products.id
where rating=5 group by department;
+------------+-------------+
| department | total_price |
+------------+-------------+
| Clothes    |       36.00 |
| Toys       |       90.00 |
+------------+-------------+

Но это кажется таким глупым взломом. Есть ли лучший способ сделать это без подзапроса? Спасибо!

Ответы

Ответ 1

Использование:

  SELECT p.department,
         SUM(p.price) AS total_price
    FROM PRODUCTS p
    JOIN (SELECT DISTINCT 
                 r.product_id,
                 r.rating
            FROM RATINGS r) x ON x.product_id = p.id
                             AND x.rating = 5
GROUP BY p.department

Технически это не использует подзапрос - он использует производную таблицу/встроенное представление.

Отметьте это как сообщество wiki, потому что какая-то обезьяна держит меня вниз, хотя это на 100% правильно.

Ответ 2

Основная причина, по которой вам трудно найти решение, заключается в том, что схема, представленная, в корне ошибочна. Вы не должны позволять таблице иметь две строки, которые являются полными дубликатами друг друга. Каждая таблица должна иметь возможность однозначно идентифицировать каждую строку, даже если это комбинация всех столбцов. Теперь, если мы изменим таблицу ratings так, чтобы она имела столбец AUTO_INCREMENT с именем Id, проблема проще:

Select products.department, Sum(price) As total_price
From products
    Left Join ratings As R1
        On R1.product_id = products.id
            And R1.rating = 5
    Left Join ratings As R2
        On R2.product_id = R1.product_id
            And R2.rating = R1.rating
            And R2.Id > R1.Id
Where R2.Id Is Null
Group By products.department

Ответ 3

Вы можете выполнить два запроса. Первый запрос:

SELECT DISTINCT product_id FROM ratings WHERE rating = 5;

Затем возьмите каждый из этих идентификаторов и вручную поместите их во второй запрос:

SELECT   department, Sum(price) AS total_price
FROM     products
WHERE    product_id In (1,2,3,4)
GROUP BY department;

Это обход для невозможности использования подзапросов. Без них невозможно устранить дубликаты записей, вызванные соединением.

Ответ 4

Я не могу придумать никакого способа сделать это без подзапроса где-нибудь в запросе. Возможно, вы можете использовать представление для маскировки использования подзапроса.

Запрет на то, что лучше всего, вероятно, найти минимальный набор данных, необходимый для расчета и сделать это в интерфейсе. Независимо от того, зависит ли это от ваших конкретных данных - сколько строк и т.д.

Другой вариант (на самом деле, может быть, это лучший...) - это получить новую ORM или вообще обойтись без нее;)

Это представление позволит вам обойти подзапрос:

CREATE VIEW Distinct_Product_Ratings
AS
    SELECT DISTINCT
        product_id,
        rating
    FROM
        Ratings