MySQL 计数分组依据

发布于 2024-11-18 10:23:27 字数 556 浏览 2 评论 0原文

我有 3 个表，订单（orders_id，日期，...），产品（product_id，product_name，product_price）和 order_products（product_id，orders_id，product_id，products_quantity），我需要对产品进行分组，以便它们按产品显示，每个产品的总数量，以便网店经理更容易了解每个产品已订购多少件商品。

我在思考正确的 sql 语法时遇到了一些麻烦，我不断遇到 group by 问题，我需要一些帮助。

这就是我到目前为止所做的

select *, op.products_quantity as pquant, count(*) as `count`
from orders o
left join orders_products op on o.orders_id = op.orders_id
left join products p on op.products_id = p.products_id 
group by op.orders_products_id
order by op.products_id desc;

原文

I have 3 tables, orders(orders_id, date, ...), products (product_id, product_name, product_price) and order_products (product_id, orders_id, product_id, products_quantity) and I need to group the products so that they are displayed by product and total quantity per product to make it easier for the eshop manager to know how many items per product have been ordered.

I'm having a little bot of trouble thinking of the correct sql syntax, I keep bumping into group by issues and i'd like some help.

This is what I've done so far

select *, op.products_quantity as pquant, count(*) as `count`
from orders o
left join orders_products op on o.orders_id = op.orders_id
left join products p on op.products_id = p.products_id 
group by op.orders_products_id
order by op.products_id desc;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我偏爱纯白色 2024-11-25 10:23:27

看看你有什么，你正在计算订单，而不是对订单数量求和。

所以，

如果你有

订单，

monday 5 potatoes
tuesday 2 carrots
wednesday 3 potatoes

你想要

potatoes 8
carrots 2

在这种情况下你想做的，

select sum(quantity),item from orders group by item

我不太明白orders_products和orders之间的区别是什么。

也许一些样本数据会有帮助？

Looking at what you have, you're counting orders, not summing the quantity of the orders..

if you had

orders

monday 5 potatoes
tuesday 2 carrots
wednesday 3 potatoes

You wanted

potatoes 8
carrots 2

in which case you'd want to do

select sum(quantity),item from orders group by item

I didnt quite see what the differende between orders_products and orders was.

Maybe a bit of sample data would help?

回复收藏 0 原文

上课铃就是安魂曲 2024-11-25 10:23:27

Select p.Product_name,sum(prodcuts_quantity) as OrderedQuantity from 
Order_products op join
Products p on p.Product_id = op.product_id
group by p.Product_name

Select p.Product_name,sum(prodcuts_quantity) as OrderedQuantity from 
Order_products op join
Products p on p.Product_id = op.product_id
group by p.Product_name

回复收藏 0 原文

噩梦成真你也成魔 2024-11-25 10:23:27

如果您需要每个产品的总数量和总订单，则可以按以下方式执行

SELECT p.*,op.total_order,op.total_quantity FROM PRODUCT LEFT JOIN (SELECT COUNT(*) AS total_order, SUM(quantity) AS total_quantity FROM order_products GROUP BY Product_id) AS op ON p.id = op.product_id

回复收藏 0 原文

疧_╮線 2024-11-25 10:23:27

这应该可行，使用 SUM，而不是 COUNT：

SELECT
    *,
    SUM(op.products_quantity) 
FROM
    orders AS o
    LEFT JOIN orders_products AS op ON o.orders_id  = op.orders_id 
    LEFT JOIN products AS p ON op.products_id = p.products_id   
GROUP BY p.products_id 
ORDER BY p.products_id  DESC

This should work, use SUM, not COUNT:

SELECT
    *,
    SUM(op.products_quantity) 
FROM
    orders AS o
    LEFT JOIN orders_products AS op ON o.orders_id  = op.orders_id 
    LEFT JOIN products AS p ON op.products_id = p.products_id   
GROUP BY p.products_id 
ORDER BY p.products_id  DESC

回复收藏 0 原文

~没有更多了~