单个查询中的MySQL JOIN和COUNT [英] MySQL JOIN and COUNT in single query

问题描述

我正在尝试将2个表连接在一起并获取外键的数量... 对不起，但是我真的不怎么解释自己，所以让我示范一下:

I'm trying to join 2 tables together and get the count of foreign keys... I'm sorry, but I don't really how to explain myself, so let me demonstrate:

我有1个表"orders"(订单)，其中包含以下字段:

I have 1 table, 'orders', for orders, with the following fields:

id, f_name, l_name, credit_card, ETC.

然后，我对订单中的商品有一个"orders_details"表，如下所示:

Then, I have an 'orders_details' table for the items in the order, like so:

id, order_id, product_id, qty

现在，我想运行一个查询，将2个表连接起来，在orders表的每一行中获得1行，并在列中告诉我每个订单中有多少产品.

Now, I want to run a query joining the 2 tables, getting 1 row per each row in the orders table, with a column telling me how many products are in each order.

有人知道如何实现这一目标吗?

Anybody know how to achieve this?

P.S.我还希望能够获得订单的所有数量"的总数(我不想为每个订单运行单独的查询).

P.S. I'd also like to be able to get the total of all the 'qty' for the orders (I don't want to run a separate query for each order).

推荐答案

SELECT o.id, o.f_name, o.l_name, COUNT(od.id), COALESCE(SUM(od.qty), 0)
FROM orders o
LEFT JOIN order_details od ON o.id = od.order_id
GROUP BY o.id, o.f_name, o.l_name

这篇关于单个查询中的MySQL JOIN和COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！