使用 MySQL 通过 JOIN 获取 GROUP BY 中的 SUM

我在 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 |
+------------+-------------+

但这感觉像是一个愚蠢的黑客行为。有没有更好的方法在没有子查询的情况下做到这一点？谢谢！

Use:

  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

从技术上讲，这不使用子查询 - 它使用派生表/内联视图。