MySQL Select Count of Duplicate Value In Relation Table

Question

我有3个表:食品、order_detail和订单

下面是表foods的记录:

id    | name       | type
------------------------------
F01   | Omelette   | Breakfast
F02   | Burger     | Breakfast
F03   | Satay      | Lunch
F04   | Fried Rice | Dinner

下面是表order_detail的记录:

food_id    | order_id
-----------------------------
F01        | T01
F04        | T01
F02        | T02
F03        | T03
F03        | T04

下面是orders表的记录:

order_id    | date       | qty
---------------------------------
T01         | 2017-05-01 | 2
T02         | 2017-05-02 | 1
T03         | 2017-05-05 | 1
T04         | 2017-05-07 | 1

我想显示按食物类型分组的计数订单细节。我预料到这个结果:

type       | total_order
-------------------------
Breakfast  | 2
Lunch      | 2
Dinner     | 1

这是我的方法，但它仍然没有显示出预期的结果。

SELECT
f.type,
(SELECT COUNT(*) FROM order_detail od WHERE f.id = od.food_id) AS total_order
FROM foods f
LEFT JOIN order_detail od ON f.id = od.food_id
GROUP BY f.type
ORDER BY f.id

结果是:

type       | total_order
-------------------------
Breakfast  | 1
Lunch      | 2
Dinner     | 1

我怎样才能得到我想要的结果?提前感谢!

Answer 1

聚合可以在这里工作，但是您需要跨所有三个表进行连接:

SELECT f.type, COUNT(o.order_id) AS total_order
FROM foods f
LEFT JOIN order_detail od ON od.food_id = f.id
LEFT JOIN orders o ON o.order_id = od.order_id
GROUP BY f.type
ORDER BY f.id;

请注意，我们在所有三个表上执行左连接是为了不丢失任何碰巧没有订单的食物类型。

Answer 2

您可以将orderdtails添加到子选择中以获得正确的数字

SELECT
f.type,
(SELECT COUNT(*) FROM order_detail od2 
WHERE f.id = od2.food_id AND od2.order_id = od.order_id 
) AS total_order
FROM foods f
LEFT JOIN order_detail od ON f.id = od.food_id
GROUP BY f.type
ORDER BY f.id

Answer 3

WITH cte AS (
SELECT type
FROM foods
INNER JOIN order_detail ON (order_detail.food_id=foods.id)
INNER JOIN orders ON (order_detail.ord_id=orders.ord_id)
)
SELECT DISTINCT type, COUNT(type) OVER (PARTITION BY type) AS qty
FROM cte