查找购买最多的产品(Find most purchased product)

查找购买最多的产品(Find most purchased product)

以下是我的问题的简化版本。 假设我有一个产品表

products ------------- id name 1 chocolate 2 pepsi 3 apple 4 chips

和订单表

orders ------------------------------------------- id product_id quantity user_id 1 1 2 1 2 1 2 2 3 1 2 3 4 1 2 4 5 2 5 5 6 2 5 6 7 3 20 7

对于用户的每次购买，我们将在订单表中插入一行以及产品的ID和他订购的单位数

我想按购买顺序获得产品清单，即。 从大多数购买到最少购买。 但问题在于排名不仅取决于用户购买的次数（行数），还取决于在该单个订单中购买的单位数量。

这是我尝试过的

SELECT products.name FROM orders left join products ON ( orders.product_id = products.id ) GROUP by orders.product_id ORDER by count(orders.product_id) desc;

这显然是错误的，因为它给出了

chocolate pepsi apple

而不是

apple pepsi chocolate

问候

Below is a simplified version of my problem. Suppose I have a products table

products ------------- id name 1 chocolate 2 pepsi 3 apple 4 chips

and orders table

orders ------------------------------------------- id product_id quantity user_id 1 1 2 1 2 1 2 2 3 1 2 3 4 1 2 4 5 2 5 5 6 2 5 6 7 3 20 7

For every purchase by user we would insert a row in orders table along with the product's ID and how many units he ordered

I want to get the list of products in descending order of their purchase ie. starting from most purchased to the least one. But the problem is ranking depends not only on how many times it was purchased by users (row count) but also on how many units where purchased in that single order.

here is what I have tried

SELECT products.name FROM orders left join products ON ( orders.product_id = products.id ) GROUP by orders.product_id ORDER by count(orders.product_id) desc;

which is clearly wrong as it gives

chocolate pepsi apple

rather than

apple pepsi chocolate

Regards

最满意答案

您想按数量的SUM ：

SELECT products.name, SUM(orders.quantity) AS sum_quantity FROM orders LEFT JOIN products ON orders.product_id = products.id GROUP BY orders.product_id ORDER BY sum_quantity DESC;

演示（与SUM和COUNT比较）： http ： SUM / COUNT / 2/0

您目前正在使用COUNT 。 因此，您只能获得orders数量，而不能获得订购products的数量。

You want to ORDER BY the SUM of quantity:

SELECT products.name, SUM(orders.quantity) AS sum_quantity FROM orders LEFT JOIN products ON orders.product_id = products.id GROUP BY orders.product_id ORDER BY sum_quantity DESC;

demo (with comparison of SUM and COUNT): http://sqlfiddle.com/#!9/b1ec57/2/0

You are using COUNT at the moment. So you only get the number of orders but not the quantity of ordered products.