以下是我的问题的简化版本。 假设我有一个产品表
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 chipsand 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 7For 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 applerather than
apple pepsi chocolateRegards
最满意答案
您想按数量的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.
更多推荐
发布评论