在GROUP BY中使用MySQL使用JOIN获取SUM [英] Get SUM in GROUP BY with JOIN using MySQL

问题描述

我有两个表在MySQL 5.1.38。

I have two tables in 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星评级的所有产品的总价格。这样的东西。

My goal is to get the total price of all products which have a 5 star rating in each department. Something like this.

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

如果可以，我想没有子查询。一开始我尝试加入一个sum（）。

I would like to do this without a subquery if I can. At first I tried a join with a 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星评级的自行车，因此计算该价格两次由于加入。

As you can see the price for the Toys department is incorrect because there are two 5 star ratings for the Bike and therefore counting that price twice due to the join.

然后我尝试对总和加上distinct。

I then tried adding distinct to the sum.

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

但是衣服部门是关闭的，因为两个产品共享相同的价格。

But then the clothes department is off because two products share the same price.

我的解决方法涉及对产品（id）采取独特的东西，并使用它来使价格独特。

Currently my work-around involves taking something unique about the product (the id) and using that to make the price unique.

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

但这种感觉就像这样一个愚蠢的黑客。有没有更好的方法来做这个没有子查询？感谢！

But this feels like such a silly hack. Is there a better way to do this without a subquery? Thanks!

推荐答案

使用：

  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

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

Technically, this does not use a subquery - it uses a derived table/inline view.

作为社区wiki cuz有些猴子坚持downvoting我，虽然它是100％正确。

Marking this as community wiki cuz some monkey keeps downvoting me though it's 100% correct.

这篇关于在GROUP BY中使用MySQL使用JOIN获取SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！